file group backup plan

[arvin]

當資料太龐大或使用了partition table 時，由於backup & restore時間會過長，這時候file group backup 就是最好的使用點了。

整個backup plan Schedule 如下：
星期日使用full database backup：

代碼: 選擇全部

BACKUP DATABASE [TRY] TO  DISK = N'C:TRY.bak' 
WITH NOFORMAT, INIT,  NAME = N'TRY-完整 資料庫 備份', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

每天使用file group backup & transactions log backup：

代碼: 選擇全部

BACKUP DATABASE [TRY] FILEGROUP = N'fg3',  FILEGROUP = N'PRIMARY' 
TO  DISK = N'C:TRY1.bak' 
WITH NOFORMAT, INIT,  NAME = N'TRY-完整 檔案群組 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP LOG [TRY] TO  DISK = N'C:TRYLog.bak' WITH NOFORMAT, INIT,  
NAME = N'TRY-交易記錄 TRUNCATE 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

這樣的好處是在於一來若是發生於非備份檔案的損毀，也可以利用full backup + transactions log backup 進行復原

restore plan我分為兩個：
file group restore：

代碼: 選擇全部

RESTORE DATABASE [TRY] FILE = N'fg3',  FILE = N'TRY' 
FROM  DISK = N'C:TRY1.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE LOG [TRY] FROM  DISK = N'C:TRYLog.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

full backup + transactions restore：

代碼: 選擇全部

RESTORE DATABASE [TRY] FROM  DISK = N'C:TRY.bak' 
WITH  FILE = 1, NORECOVERY, NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE LOG [TRY] FROM  DISK = N'C:TRYLog.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

在做file group restore 時一定要搭配一份Transactions log restore。