MS SQL Server備份計畫

一直以來公司的資料庫都使用T-SQL做完整備份，一天一次，非常耗用空間，
當然這作法是不正確，但是因為一直沒有出事，
所以也讓我一直拖到現在才找時間研究該如何正確備份，
在MS SQL Server中可分為完整備份、差異備份、交易檔備份，
這次規劃將公司的備份變更為
1.每週日凌晨作一次完整備份
2.每日早上6點作一次差異備份
3.每日上班時間每小時作一次交易檔備份


使用下述語法，並於SQL Server Agent中加入排程自動執行，

--完全備份
DECLARE @FilePath VARCHAR(200)='D:\'+CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+'_Full.bak'
BACKUP DATABASE InnovaERP TO DISK = @FilePath

--差異備份
DECLARE @FilePath VARCHAR(200)='D:\'+CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+'_Diff.diff'
BACKUP DATABASE InnovaERP TO DISK = @FilePath
WITH DIFFERENTIAL

--備份交易紀錄檔
DECLARE @FilePath VARCHAR(200)='D:\'+CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+'_Log.trn'
BACKUP LOG InnovaERP TO DISK = @FilePath

若要還原時使用下述語法即可，

--結尾交易紀錄檔備份
DECLARE @FilePath VARCHAR(200)='D:\'+CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+'_Tail_Log.trn'
BACKUP LOG InnovaERP
TO DISK = @FilePath
WITH NORECOVERY

--完整備份回復
RESTORE DATABASE InnovaERP
FROM DISK = 'D:\20130721030000_Full.bak'
WITH NORECOVERY

--差異備份回復
RESTORE DATABASE InnovaERP
FROM DISK = 'D:\20130721050000_Diff.diff'
WITH NORECOVERY

--交易記錄檔回復
RESTORE LOGInnovaERP
FROM DISK = 'D:\20130721060000_Log.trn'
WITH NORECOVERY

回復時要注意的事項如下，
1.還原的順序為
完整備份-->全部差異備份-->最後一次差異備份後的全部交易記錄備份
2.若出現提示"記錄結尾尚未備份"，需先執行第一段的結尾備份語法
3.還原時需使用NORECOVERY參數讓資料庫於回原狀態，
於最後一個回復再使用RECOVERY參數，結束還原，恢復資料庫狀態
4.欲還原的資料庫路徑若與備份時的資料庫路徑不相符時，會發生無法還原的情況，
需先修改路徑

參考自：
觀念釐清：SQL Server 完整備份、差異備份、交易記錄備份
[SQL SERVER][Maintain]如何利用交易紀錄檔還原到某一時間點
MSDN-SQL Server 資料庫的備份與還原