SQL Server - 文件组，文件，备份，分区

创建文件组 USE [master] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_1] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_2] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_3] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_4] GO --创建文件 USE [master] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_11‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_11.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_1] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_12‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_12.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_1] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_21‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_21.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_2] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_22‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_22.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_2] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_31‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_31.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_3] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_32‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_32.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_3] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_41‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_41.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_4] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_42‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_42.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_4] GO ALTER DATABASE [DWH_Optimizing] MODIFY FILE ( NAME = N‘DWH_Optimizing_log‘, MAXSIZE = UNLIMITED) GO

备份：完整备份，差异备份，文件、文件组和日志备份（需要设置数据的恢复模式为Full）

--文件组备份
BACKUP DATABASE [DWH_Optimizing] FILEGROUP = N‘DWH_Optimizing_1‘ TO  DISK = N‘F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘DWH_Optimizing-Full Filegroup Backup‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--文件备份
BACKUP DATABASE [DWH_Optimizing] FILE = N‘DWH_Optimizing_11‘ TO  DISK = N‘F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘DWH_Optimizing-Full File Backup‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--完整备份
BACKUP DATABASE [DWH_Optimizing] TO  DISK = N‘F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘DWH_Optimizing-Full Database Backup‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--差异备份
BACKUP DATABASE [DWH_Optimizing] TO  DISK = N‘F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak‘ WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N‘DWH_Optimizing-Differential Database Backup‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--事物日志备份
BACKUP LOG [DWH_Optimizing] TO  DISK = N‘F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘DWH_Optimizing-Transaction Log  Backup‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

分区，可以将表按照某个字段进行分区

--创建分区函数
CREATE PARTITION FUNCTION [PF_BICompany](int) AS RANGE RIGHT FOR VALUES (2, 3, 4, 5)
GO

--创建分区架构
CREATE PARTITION SCHEME [PS_BICompany] AS PARTITION [PF_BICompany] TO ([DWH_Optimizing_1], [DWH_Optimizing_2], [DWH_Optimizing_3], [DWH_Optimizing_4], [PRIMARY])
GO

 

SQL Server - 文件组，文件，备份，分区

标签：files   tran   lte   pre   master   size   com   weight   div