file group backup plan
發表於 : 2009年 7月 9日, 09:35
當資料太龐大或使用了partition table 時,由於backup & restore時間會過長,這時候file group backup 就是最好的使用點了。
整個backup plan Schedule 如下:
星期日使用full database backup:
每天使用file group backup & transactions log backup:
這樣的好處是在於一來若是發生於非備份檔案的損毀,也可以利用full backup + transactions log backup 進行復原
restore plan我分為兩個:
file group restore:
full backup + transactions restore:
在做file group restore 時一定要搭配一份Transactions log restore。
整個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
代碼: 選擇全部
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
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
代碼: 選擇全部
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