FileGroup：文件组，为逻辑划分；Files：文件，为实际文件，需要指定文件属于哪个文件组。
使用多个文件的有点：可以将磁盘I/O压力分散，提供按文件和文件组（按文件和文件组进行备份需要设置数据的恢复模式为Full）进行备份。
--
创建文件组
USE
[
master
]
GO
ALTER
DATABASE
[
DWH_Optimizing
]
ADD FILEGROUP [DWH_Optimizing_1]GOALTERDATABASE[DWH_Optimizing]ADD FILEGROUP [DWH_Optimizing_2]GOALTERDATABASE[DWH_Optimizing]ADD FILEGROUP [DWH_Optimizing_3]GOALTERDATABASE[DWH_Optimizing]ADD FILEGROUP [DWH_Optimizing_4]GO--创建文件USE[master]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_11‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_11.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_1]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_12‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_12.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_1]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_21‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_21.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_2]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_22‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_22.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_2]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_31‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_31.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_3]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_32‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_32.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_3]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_41‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_41.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_4]GOALTERDATABASE[DWH_Optimizing]ADDFILE ( NAME = N‘DWH_Optimizing_42‘, FILENAME = N‘E:MSSQL11.BIS01MSSQLDATADWH_Optimizing_42.ndf‘ , SIZE = 512000KB , FILEGROWTH =50%) TO FILEGROUP [DWH_Optimizing_4]GOALTERDATABASE[DWH_Optimizing] MODIFY FILE ( NAME = N‘DWH_Optimizing_log‘, MAXSIZE = UNLIMITED)
GO
备份：完整备份，差异备份，文件、文件组和日志备份（需要设置数据的恢复模式为Full）
--
文件组备份
BACKUP
DATABASE
[
DWH_Optimizing
] FILEGROUP = N‘DWH_Optimizing_1‘TODISK= N‘F:MSSQL11.BIS01MSSQLBackupDWH_Optimizing.bak‘WITH NOFORMAT, NOINIT, NAME = N‘DWH_Optimizing-Full Filegroup Backup‘, SKIP, NOREWIND, NOUNLOAD, STATS =10GO--文件备份BACKUPDATABASE[DWH_Optimizing]FILE= N‘DWH_Optimizing_11‘TODISK= N‘F:MSSQL11.BIS01MSSQLBackupDWH_Optimizing.bak‘WITH NOFORMAT, NOINIT, NAME = N‘DWH_Optimizing-Full File Backup‘, SKIP, NOREWIND, NOUNLOAD, STATS =10GO--完整备份BACKUPDATABASE[DWH_Optimizing]TODISK= N‘F:MSSQL11.BIS01MSSQLBackupDWH_Optimizing.bak‘WITH NOFORMAT, NOINIT, NAME = N‘DWH_Optimizing-Full Database Backup‘, SKIP, NOREWIND, NOUNLOAD, STATS =10GO--差异备份BACKUPDATABASE[DWH_Optimizing]TODISK= N‘F:MSSQL11.BIS01MSSQLBackupDWH_Optimizing.bak‘WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N‘DWH_Optimizing-Differential Database Backup‘, SKIP, NOREWIND, NOUNLOAD, STATS =10GO--事物日志备份BACKUPLOG[DWH_Optimizing]TODISK= N‘F:MSSQL11.BIS01MSSQLBackupDWH_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 RIGHTFORVALUES (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
原文：http://www.cnblogs.com/Niko12230/p/6780937.html
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！