文件組備份還原

-- 參考
USE master;
GO-- 測試的DB
CREATE DATABASE DB_Test
ON PRIMARY(NAME = DB_Test,FILENAME = 'C:\DB_Test.mdf'
),
FILEGROUP FG1 (NAME = DB_Test_FG1,FILENAME = 'C:\DB_Test_fg1.ndf'
),
FILEGROUP FG2 (NAME = DB_Test_FG2,FILENAME = 'C:\DB_Test_fg2.ndf'
)
LOG ON(NAME = DB_Test_LOG,FILENAME = 'C:\DB_Test.ldf'
)
GO-- 各文件組上的表
CREATE TABLE DB_Test.dbo.tb_primary(id int
)ON [PRIMARY];CREATE TABLE DB_Test.dbo.tb_FG1(id int
)ON FG1;CREATE TABLE DB_Test.dbo.tb_FG2(id int
)ON FG2;
GO-- 備份
BACKUP DATABASE DB_TestFILEGROUP = N'PRIMARY',FILEGROUP = N'FG1'
TODISK = N'C:\DB_Test.bak'
WITHINIT
;-- 刪除測試庫
ALTER DATABASE DB_Test
SETSINGLE_USER
WITHROLLBACK AFTER 0
;
DROP DATABASE DB_Test;
GO-- 還原測試
RESTORE DATABASE DB_TestFILEGROUP = N'PRIMARY',FILEGROUP = N'FG1'
FROMDISK = N'C:\DB_Test.bak'
;
GO-- 查詢各文件組上的表
SELECT * FROM DB_Test.dbo.tb_primary;
GO
SELECT * FROM DB_Test.dbo.tb_FG1;
GO
SELECT * FROM DB_Test.dbo.tb_FG2;
-- 這個會報錯, 因為沒有備份/還原文件組 FG2
GO-- 刪除測試
ALTER DATABASE DB_Test
SETSINGLE_USER
WITHROLLBACK AFTER 0
;
DROP DATABASE DB_Test;
GO

• Marked as answer by?