SQL Server - 檔案組，檔案，備份，分區

標籤：files   tran   lte   pre   master   size   com   weight   div   

FileGroup：檔案組，為邏輯劃分；Files：檔案，為實際檔案，需要指定檔案屬於哪個檔案組。

使用多個檔案的有點：可以將磁碟I/O壓力分散，提供按檔案和檔案組（按檔案和檔案組進行備份需要設定資料的復原模式為Full）進行備份。

--建立檔案組USE [master]GOALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_1]GOALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_2]GOALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_3]GOALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_4]GO--建立檔案USE [master]GOALTER 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]GOALTER 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]GOALTER 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]GOALTER 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]GOALTER 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]GOALTER 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]GOALTER 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]GOALTER 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]GOALTER 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 = 10GO--檔案備份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 = 10GO--完整備份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 = 10GO--差異備份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 = 10GO--事物記錄備份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 = 10GO

分區，可以將表按照某個欄位進行分區

--建立分區函數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 - 檔案組，檔案，備份，分區