SQLSERVER 進階資料庫修復-結尾紀錄備份

資料庫損壞時，最佳的建議是透過備份檔還原，將資料庫回復到正常的狀態，當然，前提是備份檔是正常的。

不過，使用資料庫備份還原，最大的問題是可能會有一小段時間的資料遺失(RPO > 0)。

舉例來說，通常的備份策略是一天或一週做一次全備份，而在兩個全備份的期間，則定時做差異或交易紀錄備份。

因此，當資料庫損壞時，可能會有一小段時間是沒有備份的，這一小段時間的交易紀錄，稱為「結尾紀錄 tail-log」。

   

  

早期初學時，遇到像圖中這種狀況，因對DB不夠了解只能自認倒楣，然後用已經備份的檔案還原，並且通知使用者資料庫壞了，而且可能會有一小段時間的資料損失，接著就是一連串的檢討及補key資料。

後來為了避免這種狀況，就開始學習建HA，主要目的就是為了DB災害發生時不再有資料損失，並且降低DB停止服務的時間，也就是大家常講的RTO、RPO。

隨著對DB逐漸的熟悉，就想到如果在DB損壞的情況下還能把最後這段結尾紀錄備份出來，那麼就算沒有HA，光靠備份與還原也能做到「資料零損失」，只是回復的時間會比較長。

經過斷斷續續的測試及google大神的幫助，我發現，只要是交易紀錄檔(ldf)沒壞掉，即使是DB壞的無法online了，都有很大的可能將結尾紀錄備份出來，再配合之前的定期備份檔進行還原，達到「資料零損失」的目地。

因此，當資料庫損壞時，如果你的DB沒有HA，你應在第一時間，嘗試備份「結尾紀錄 tail-log」，以最大程度的減少資料的損失。

資料庫損壞，但仍在線上可用

第一種狀況是資料庫損壞，但DB仍然ONLINE中可以使用。

這時候，應先將DB設為單一使用者模式，並首先嘗試以一般指令備份交易紀錄，如果成功，那麼就可以使用之前的備份加上結尾紀錄備份，將資料庫還原至最近的時間點。

如果正常備份交易紀錄失敗，則可試著在指令後加上 with no_truncate，例如：

backup log MyDB to disk='c:\test\MyDB.trn' with noinit

go

/*

備份失敗

訊息 945，層級 14，狀態 2，行 1

檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'MyDB'。詳細資訊請參閱 SQL Server 錯誤記錄檔。

訊息 3013，層級 16，狀態 1，行 1

BACKUP LOG 正在異常結束。

*/

資料庫損壞，無法執行結尾紀錄備份，我們在backup log指令後，使用no_truncate

backup log MyDB to disk='c:\test\MyDB.trn' with noinit,no_truncate 

go --OK，備份成功

接著就可以使用restore指令，將資料庫還原至最後的時間點。

以下節錄微軟官網對於no_truncate的解釋

NO_TRUNCATE

指定不截斷記錄，並使 Database Engine 嘗試進行備份，而不論資料庫狀態為何。 因此，利用 NO_TRUNCATE 建立的備份可能會有不完整的中繼資料。 在資料庫已損毀的情況下，您可以利用這個選項來進行記錄的備份。

BACKUP LOG 的 NO_TRUNCATE 選項相當於同時指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。

未使用 NO_TRUNCATE 選項時，資料庫必須處於 ONLINE 狀態。 如果資料庫處於 SUSPENDED 狀態，您就能透過指定 NO_TRUNCATE 來建立備份。 但是，如果資料庫處於 OFFLINE 或 EMERGENCY 狀態，即使設定了 NO_TRUNCATE，也不允許 BACKUP。

資料庫損壞，且己不可用

第二種狀況是資料庫損壞，而且已經不可用。

例如DB損壞SQL SERVER自動將DB離線，或者DB被置於Suspect、OFF-LINE，或者用no_truncate也無法備份…等。這些情況下DB基本上已無法存取，也無法備份交易紀錄。

在這種情況下，有可能是mdf損壞，但ldf是好的。我們可以試著用「hack-attach」的方法，將結尾紀錄備份出來。

Hack-Attach

在另一台相同版本的Server

一、建立一個同名的資料庫（dummy DB），包含mdf及ldf的名稱都相同。

二、將dummy DB離線。ALTER DATABASE dummyDB SET OFFLINE;

三、刪除dummy DB的mdf及ldf等所有資料檔。

四、將損壞的DB的ldf檔，複製到dummy DB的ldf檔所在目錄，取代新建的ldf。

五、ALTER DATABASE dummyDB SET ONLINE; --會發生錯誤，忽略它

六、BACKUP LOG dummyDB TO DISK = '......'  WITH NO_TRUNCATE

經過上述步驟後，應可將結尾紀錄備份出來，就可以用之前的備份檔加上結尾紀錄備份，將資料庫還原至最近的時間點。

其實這個方法就是做一個假的資料庫，在OFF-LINE後將要做結尾紀錄備份LDF覆蓋掉新建的LDF，接著將資料庫ONLINE，最後將交易紀錄備份出來。這有點像是欺騙SQL SERVER，故稱為hack-attach.

如果你的DB沒有HA，而僅是靠著備份檔在做備援，那麼你更應該要熟悉或知道這些方法，當災害發生時儘量減少資料的損失。