[SQL SERVER][Maintain]還原問題
或許大家資料庫的備份檔案數量相當多，
可能包含第一次完整備份、第一次差異備份、第一小時交易紀錄檔備份..等，
但不管如何，備份檔案名稱一定要夠直覺且可以馬上判斷出那個時間點、備份類型，
因為還原檔案順序是相當重要的，而資料庫在完整或大量紀錄復原模式下，
SQL2005以後的版本幾乎都會要求還原時先備份記錄結尾，
以便擷取未備份的交易紀錄檔。
這篇紀錄一下以前學習還原所遭遇問題。
我個人習慣的備份檔案名稱如下
完整:dbName_Full_sysdate.bak
差異:dbName_Diff_sysdate.bak or dbName_Diff_sysdate.diff
交易紀錄檔:dbName_Log_sysdate.bak or dbName_sysdate_Log.trn
下面是我簡單執行資料庫備份過程
--完整備份
backup database mytest to disk='D:\mybktest\mytest_full.bak' with format, compression
--新增資料
insert into mytbl values(1,'1')
insert into mytbl values(2,'2')
--備份交易紀錄檔
backup log mytest to disk='D:\mybktest\mytest_1.trn' with format, compression
--新增資料
insert into mytbl values(3,'3')
insert into mytbl values(4,'4')
insert into mytbl values(5,'5')
--備份交易紀錄檔
backup log mytest to disk='D:\mybktest\mytest_2.trn' with format, compression
--新增資料
insert into mytbl values(6,'6')
--差異備份
backup database mytest to disk='D:\mybktest\mytest_1_dif.bak' with format,compression,differential
--新增資料
insert into mytbl values(7,'7')
--備份交易紀錄檔
backup log mytest to disk='D:\mybktest\mytest_3.trn' with format, compression
--差異備份
backup database mytest to disk='D:\mybktest\mytest_2_dif.bak' with format,compression,differential
insert into mytbl values(8,'8')
--完整備份
backup database mytest to disk='D:\mybktest\mytest_2_full.bak' with format, compression
--新增資料
insert into mytbl values(9,'9')
--備份交易紀錄檔
backup log mytest to disk='D:\mybktest\mytest_4.trn' with format, compression
--新增資料
insert into mytbl values(10,'11')
insert into mytbl values(11,'11')
--差異備份
backup database mytest to disk='D:\mybktest\mytest_21_dif.bak' with format,compression,differential
insert into mytbl values(12,'12')
接下來我會模擬幾個常見的錯誤狀況
--需求1:還原mytbl ID <=2
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_2_full.bak' with norecovery
restore log mytest from disk='D:\mybktest\mytest_1.trn' with recovery
錯誤:還原時因為使用了錯誤的完整備份組，所以導致LSN順序錯誤。
修正:使用正確的完整備份組(mytest_full.bak)
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_full.bak' with norecovery
restore log mytest from disk='D:\mybktest\mytest_1.trn' with recovery
--需求2:還原mytbl ID <=9
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_2_full.bak' with recovery
錯誤:雖然還原成功，但資料還是不完整(ID<=8)，因為忘記還原交易紀錄檔。
修正:增加還原交易紀錄檔。
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_2_full.bak' with norecovery
restore log mytest from disk='D:\mybktest\mytest_4.trn' with recovery
--需求3:還原mytbl ID <=6
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_full.bak' with norecovery
restore database mytest from disk='D:\mybktest\mytest_1_dif.bak' with norecovery
restore log mytest from disk='D:\mybktest\mytest_2.trn' with recovery
錯誤:還原順序錯誤，因為先還原了差異備份導致還原交易紀錄檔造成LSN順序錯誤。
修正:調整還原備份組順序，完整>交易紀錄檔(這期間所有檔案)>差異備份
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_full.bak' with norecovery
restore log mytest from disk='D:\mybktest\mytest_1.trn' with norecovery
restore log mytest from disk='D:\mybktest\mytest_2.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_1_dif.bak' with recovery
或者 完整>差異備份
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_full.bak' with norecovery
restore database mytest from disk='D:\mybktest\mytest_1_dif.bak' with recovery
--需求4:還原mytbl ID <=11
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_21_dif.bak' with recovery
錯誤:因為錯誤使用差異備份直接還原所導致。
修正:完整>差異備份
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_2_full.bak' with norecovery
restore database mytest from disk='D:\mybktest\mytest_21_dif.bak' with recovery
--需求5:還原mytbl ID <=7
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_full.bak'
restore database mytest from disk='D:\mybktest\mytest_2_dif.bak'
錯誤:還原完整備份未使用 with norecovery，導致不能正確還原差異備份或紀錄。
修正:還原完整備加上with norecovery
use master
backup log mytest to disk='D:\mybktest\mytest_taillog.trn' with norecovery
restore database mytest from disk='D:\mybktest\mytest_full.bak' with norecovery
restore database mytest from disk='D:\mybktest\mytest_2_dif.bak' with recovery
ps:這裡無法將資料還原到 ID<=12，因為後面沒有相關的備份檔案。
參考
