数据库文件过大时就要进行数据分区，就是讲数据库拆分到多个文件组中。已方便数据文件管理，提高数据库的读取效能，多文件组如何进行数据库的备份和还原呢，今天主要做多文件组数据库的备份和还原实验。
第一步
创建数据库qhw_test 数据库包括一个userinfo 数据表，userinfo数据表根据id做分区 包括一个主分区
，五个次分区，主分区包括qhw_test,data2两个文件，
数据表脚本如下
CREATE TABLE [dbo].[userinfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[CreateTime] [datetime] NOT NULL,
CONSTRAINT [PK_userinfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[userinfo] ADD CONSTRAINT [DF_userinfo_UserName] DEFAULT ('') FOR
[UserName]
GO
ALTER TABLE [dbo].[userinfo] ADD CONSTRAINT [DF_userinfo_CreateTime] DEFAULT (getdate())
FOR [CreateTime]
GO
往数据表中写入数据
第二部
然后执行数据库备份
使用完整备份模式一次备份数据库文件，先备份主分区，一次备份其他次分区，最后做事务日志备份
备份完成后，将备份文件转移至另一台机器
第三部
进行还原操作，先还原主分区，依次还原次分区，还原事务日志，注意NORECOVERY,
RESTORE DATABASE qhw_test FILEGROUP = 'PRIMARY' FROM DISK = 'D:akakqhw_test1.bak'
WITH FILE = 1,
move 'qhw_test' to 'd:akqhw_test.mdf',
move 'data2' to 'd:akdata2.ndf',
move 'qhw_test_log' to 'd:akqhw_test_log.ldf'
,
NORECOVERY,REPLACE,STATS = 10
RESTORE DATABASE qhw_test FILEGROUP = 'g1' FROM DISK = 'D:akakg1.bak' WITH FILE = 1,
move 'g1' to 'd:akg1.ndf',
NORECOVERY,REPLACE,STATS = 10
RESTORE DATABASE qhw_test FILEGROUP = 'g2' FROM DISK = 'D:akakg2.bak' WITH FILE = 1,
move 'g2' to 'd:akg2.ndf',
NORECOVERY,REPLACE,STATS = 10
RESTORE DATABASE qhw_test FILEGROUP = 'g3' FROM DISK = 'D:akakg3.bak' WITH FILE = 1,
move 'g3' to 'd:akg3.ndf',
NORECOVERY,REPLACE,STATS = 10
RESTORE DATABASE qhw_test FILEGROUP = 'g4' FROM DISK = 'D:akakg4.bak' WITH FILE = 1,
move 'g4' to 'd:akg4.ndf',
NORECOVERY,REPLACE,STATS = 10
RESTORE DATABASE qhw_test FILEGROUP = 'g5' FROM DISK = 'D:akakg5.bak' WITH FILE = 1,
move 'g5' to 'd:akg5.ndf',
NORECOVERY,REPLACE,STATS = 10
RESTORE LOG qhw_test
FROM DISK = 'D:akaklog.bak'
WITH NORECOVERY
GO
RESTORE DATABASE qhw_test
WITH RECOVERY
RESTORE DATABASE [' + @DataBaseName + ']
WITH RECOVERY