《SQL Server企業級平台管理實踐》讀書筆記——關於SQL Server資料庫的還原方式

標籤：style   blog   http   color   ar   os   使用   sp   for   

原文:《SQL Server企業級平台管理實踐》讀書筆記——關於SQL Server資料庫的還原方式

本篇是繼上篇的備份方式，本篇介紹的是還原方案，在SQL Server在2005以上現有的還原方案一般分為以下4個層級的資料還原：

1、資料庫完整還原層級：

還原和恢複整個資料庫。資料庫在還原和恢複操作期間會處於離線狀態

2、資料檔案層級

還原和恢複一個資料檔案或一組檔案。在檔案還原過程中，包含相應檔案的檔案組在還原過程中自動變為離線狀態。訪問離線檔案組的任何嘗試都會導致錯誤。但是其他檔案組還能保持線上。

3、資料頁層級

在完整復原模式或大量記錄復原模式下，可以對資料庫指定還原特定的一個或一些資料頁面，無須吧整個資料或整個檔案都重新建立一遍。對於只損壞了很少一部分頁面的大資料庫，這種還原方式可以大大節約還原時間。

4、段落層級

在大型資料庫裡，往往包含了若干個檔案或檔案組。使用分次還原，可以使得資料庫在還原了一部分檔案或檔案組以後，這部分資料就可以訪問，從而達到降低資料庫離線時間的目的。

這裡有一點需要注意：SQL Server不允許使用者備份或還原單個表

對於不同的復原模式所支援的還原方式也不一樣，下表我們列出不同的方法

還原方案	在簡單復原模式下	在完整/大量記錄復原模式下
資料庫完整還原	這是最基本的還原策略。 資料庫完整還原可能涉及完整資料庫的簡單還原和恢複。 另外，完整的資料庫還原還可能涉及還原完整Database Backup， 以及還原和恢複差異備份	這是基本的還原策略。 資料庫完整還原涉及還原完整Database Backup或差異備份（如果有）， 以及還原所有後續記錄備份（按順序）。通過恢複並還原上一次 記錄備份（RESTORE WITH RECOVERY），完成資料庫完整還原
檔案還原	只能還原損壞的唯讀檔案，但是不還原整個資料庫，所以 實用性不是很強	能夠還原一個或多個檔案，而不還原整個資料庫。可以在資料庫處於離線 狀態或資料庫保持線上狀態（對於SQL Server 2005）時執行檔案還原。 在檔案還原過程中，包含正在還原的檔案的檔案組一直處於離線狀態。 其它檔案組有可能被訪問
分頁還原	不適用	還原損壞的頁面，可以在資料庫處於離線狀態或資料庫保持線上狀態， 在分頁還原過程中，正在還原的頁面一直處於離線狀態。 必須具有完整的記錄備份鏈（包含當前記錄檔），並且必須回複所有的這些 記錄備份，以使頁面與當前記錄檔保持一致
分次還原	按檔案組層級並從主檔案組和所有讀寫輔助檔案組凱斯， 分階段還原和恢複資料庫	按檔案組層級並從主檔案組開始，分階段還原和恢複資料庫

無論以何種方式還原資料，在恢複資料庫之前，SQL Server資料庫引擎都會保證整個資料庫在邏輯上的一致性。例如，還原一個檔案以後，必須恢複完整的一套記錄檔備份，以便將該檔案裡的事務前滾足夠長度，與資料庫保持一致，才能恢複該檔案並使其線上。

1、資料庫完整還原

將一個資料庫從無到有，完整地還原出來，是最常使用的還原作業。在簡單的情況下，還原作業只需要一個完整Database Backup、一個差異Database Backup和後續記錄備份。很容易構造一個正確的還原順序。例如，若要將整個資料庫還原到故障點。需要首先備份活動交易記錄（日誌的“尾部”）。然後，按照備份的建立順序還原最新的完整Database Backup、最新的差異備份（如果有）及所有後續日子備份。如果來源資料庫是簡單模式，則沒有響應的記錄備份。恢複工作僅限於還原一個完整Database Backup，以及最後一個差異備份。

在這種模式下，使用者經常遇到的一個挑戰，是發生災難後如何將資料庫恢複到一個特定的復原點。例如，一個關鍵資料表被人在中午12點01分誤刪。如何將其恢複到12點鐘的那個狀態？

SQL Server能夠很好的支援這類需求，可它是通過恢複記錄檔到指定復原點的方式來實現的。所以，它有幾個先決要求。而且是在災難發生之前，資料庫就必須滿足一下條件：

1、資料庫的復原模式必須是完整復原模式

2、災難發生前，資料庫曾經做過一個完整Database Backup（或有一套完整的檔案備份）

3、在上次完整Database Backup後，如果做過任何記錄備份，這些記錄備份現在每個都能找到。

符合以上幾點要求的資料庫，就可以使用備份恢複方法將資料庫恢複到完整備份後的任意一個時間點。

恢複步驟如下：

1、備份活動交易記錄（也稱為日誌尾部）。此操作將建立尾記錄備份。如果活動交易記錄在災難發生後變得不可用，則該日誌部分的所有事務都將丟失

2、還原最新完整Database Backup，而且不做事務恢複

採用以下語句執行：RESTORE DATABASE database_name FROM back_dervice WITH NORECOVERY

3、如果存在差異備份，則還原最新的差異備份，而不做事務恢複

RESTORE DATABASE databse_name WITH NORECOVERY

4、從還原備份後建立的第一個交易記錄備份開始，使用NORECOVERY依次還原日誌。

RESTORE LOG  database_name FROM back_logdervice

5、恢複資料庫到某個時間點，此步驟也可以與還原上一次記錄備份結合使用

RESTORE DATABSE databse_name WITH STOPAT=‘12：00‘, RECOVERY

下面我們建立一測試庫，然後類比執行一下備份、還原的整個過程，指令碼如下

--做一次資料庫全備份BACKUP DATABASE [TestDB] TO  DISK = N‘F:\SQLTEST\TestDB.bak‘ WITH NOFORMAT,NOINIT,  NAME = N‘TestDB-完整 資料庫 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--第二個類比增量備份BACKUP DATABASE [TestDB] TO  DISK = N‘F:\SQLTEST\TestDBUpdate.trn‘ WITH DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N‘TestDB-差異 資料庫 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--第三個交易記錄備份BACKUP LOG [TestDB] TO  DISK = N‘F:\SQLTEST\TestDB29141107.trn‘ WITH NOFORMAT, NOINIT,  NAME = N‘TestDB-交易記錄  備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--第四個交易記錄備份BACKUP LOG [TestDB] TO  DISK = N‘F:\SQLTEST\TestDB29141108.trn‘ WITH NOFORMAT, NOINIT,  NAME = N‘TestDB-交易記錄  備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--最後一次事務結尾日誌（尾部日誌），並且資料庫處於“還原”狀態BACKUP LOG [TestDB] TO  DISK = N‘F:\SQLTEST\TestDBLastLog.trn‘ WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N‘TestDB-交易記錄  備份‘, SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10GO

然後我們按照上面描述的順序進行一次，還原過程，代碼如下

--從備份中恢複第一個全量備份RESTORE DATABASE [TestDB] FROM  DISK = N‘F:\SQLTEST\TestDB.bak‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GO--恢複第二個增量備份RESTORE DATABASE [TestDB] FROM  DISK = N‘F:\SQLTEST\TestDBUpdate.trn‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GO--恢複第三個交易記錄備份RESTORE LOG [TestDB] FROM  DISK = N‘F:\SQLTEST\TestDB29141107.trn‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GO--恢複第四個交易記錄備份RESTORE LOG [TestDB] FROM  DISK = N‘F:\SQLTEST\TestDB29141108.trn‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GO--恢複第五個尾部交易記錄備份，並且採用recovery上線該庫RESTORE LOG [TestDB] FROM  DISK = N‘F:\SQLTEST\TestDBLastLog.trn‘ WITH  FILE = 1, RECOVERY, NOUNLOAD,  STATS = 10GO

如果備份交易記錄尾部日誌的時候，如果提示當前多使用者登入，可以採用以下指令碼先將資料庫設定成單使用者，然後還原之後再改成多使用者模式

--設定成單一使用者模式ALTER DATABASE TESTDB SET SINGLE_USERGO--設定成多使用者模式ALTER DATABASE TESTDB SET MULTI_USERGO

此種備份還原的方式是比較簡單並且易用的一種方式，也是應用最廣泛使用的還原方案。如果資料庫是超大資料庫，這個方案已經基本能滿足需求了。

但是這個方案也有它自己的缺點，最大的缺點就是要做一次資料庫的完整備份恢複。這一步在空間上和時間上，都是代價昂貴的一步。

1、在時間上，SQL Server需要很長的時間來重建整個資料庫。而且在這個過程中，資料庫都是不能訪問的。時間的長短，基本由硬碟的速度決定。一個上TB的資料庫，做一個完整恢複可能需要將近一天的時間。這個等待時間，是很多系統不能接受的。

2、在空間上，一個完整備份的大小和資料庫已經使用大小基本一致。如果備份是要放在硬碟上，基本上就需要能提供2倍的空間。一份放資料庫，一份放備份。

其實很多時候，空間的問題還不大，因為現在的儲存已經提高至很大，可是時間上的等待往往沒法讓人接受。這時候資料庫管理員可以根據災難的嚴重程度，以及手裡的備份檔案，以及資料的結構，看看採取別的方式來縮短這個恢復。下面這些方案都需要資料庫預先選擇完整復原模式，操作起來比較複雜，但是用得好可以大大的縮短資料庫離線時間。

2、檔案還原

一個資料庫會有若干檔案和檔案組。如果損壞只是集中在其中一個檔案或檔案組上，而其他大部分資料檔案都沒有損壞，使用傳統的資料庫還原方案將所有的檔案重建，這樣是浪費時間的。如果SQL Server只是把壞掉的那個資料檔案或者檔案組重建，肯定能夠省下來很多時間。

可是，資料庫的事務修改是會分布在各個資料檔案上的。如果用備份只恢複其中一個檔案，而其他檔案不恢複，那他們的狀態一定會不一致。一定會有許多修改在被恢複的檔案裡沒有被包含進來。這樣的資料庫是無法使用的。為了使新恢複的檔案能夠自動回復備份以後做的修改，SQL Server需要藉助交易記錄。首先在恢複之前，必須做一次當前資料庫的記錄備份（即所謂的尾記錄備份）。恢複所有記錄備份，SQL Server就能利用前滾的方法將資料檔案恢複到一致的時間點。

該過程如下步驟

1、建立活動交易記錄的尾記錄備份

這一步是檔案還原的至關重要的一步。對於離線檔案還原，在檔案還原之前必須使用先進行一次尾部記錄備份。對於線上檔案還原，在檔案還原之後必須始終先進行一次記錄備份。此記錄備份對於將檔案恢複到與資料庫的其餘部分一致的狀態至關重要。如果因為日誌已損壞而無法執行此操作，則檔案還原無法進行，必須還原整個資料庫。

這也是檔案備份這種方式不能完全保證資料完整性的原因

2、從每個損壞的檔案的最新檔案備份還原相應的檔案。

3、針對每個還原的檔案，還原最近的差異檔案備份（如果有）。

4、按順序還原交易記錄備份，從覆蓋最早還原檔案的備份開始，到此步驟1中建立的尾記錄備份結束。

雖然這裡恢複的記錄備份針對的是整個資料庫，但是交易記錄備份的恢複會是比較迅速的，因為這裡僅處理還原檔案所做的更改。與還原整個資料庫相比，這樣做還是比較會節省很多時間。

下面我們通過一個詳細的例子，來進行這個備份還原的過程，我先將一個資料庫建立幾份檔案組，如下：

下面我們先將這幾個檔案組進行備份，當然這個過程一般在計劃任務中進行，我們先類比這個過程：

--依次備份所有的資料檔案BACKUP DATABASE [Sales] FILEGROUP = N‘PRIMARY‘ TO  DISK = N‘F:\SQLTest\SalesOneFile.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-完整 檔案組 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOBACKUP DATABASE [Sales] FILEGROUP = N‘FG1‘ TO  DISK = N‘F:\SQLTest\SalesFileTwo.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-完整 檔案組 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOBACKUP DATABASE [Sales] FILEGROUP = N‘FG2‘ TO  DISK = N‘F:\SQLTest\SalesFileThree.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-完整 檔案組 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOBACKUP DATABASE [Sales] FILEGROUP = N‘FG3‘ TO  DISK = N‘F:\SQLTest\SalesFileFour.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-完整 檔案組 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOBACKUP DATABASE [Sales] FILEGROUP = N‘FG4‘ TO  DISK = N‘F:\SQLTest\SalesFileFive.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-完整 檔案組 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--先添加一個增量備份檔案BACKUP DATABASE [Sales] FILEGROUP = N‘FG4‘ TO  DISK = N‘F:\SQLTest\SalesFileFiveUpdate.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-完整 檔案組 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--備份一個交易記錄檔BACKUP LOG [Sales] TO  DISK = N‘F:\SQLTest\SalesLog.trn‘ WITH NOFORMAT, NOINIT,  NAME = N‘Sales-交易記錄  備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO

假設這個時候，FG4檔案組壞掉了，我們要採用檔案恢複的模式，進行資料恢複，我們採取線上恢複的方式

--線上還原檔案組FG4也就是第四個檔案RESTORE DATABASE [Sales] FILE = N‘File4‘ FROM  DISK = N‘F:\SQLTest\SalesFileFive.bak‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10GO--經過上面的步驟該檔案已經還原上了，但是檔案組FG4為NoRECOVERY狀態--我們這時候需要進行日誌尾部備份，以確保該檔案離線時候所做的事務能正確執行--我們採用Copy_OnlyBACKUP LOG [Sales] TO  DISK = N‘F:\SQLTest\SaleslastLog.trn‘ WITH Copy_onlyGO--我們還原一系列的記錄備份檔案,最後將我們上面擷取的最後的記錄備份恢複RESTORE LOG [Sales] FROM  DISK = N‘F:\SQLTest\SalesLog.trn‘ WITH  NORECOVERYGORESTORE LOG [Sales] FROM  DISK = N‘F:\SQLTest\SaleslastLog.trn‘ WITH  RECOVERYGO

到此檔案還原的方式實現完畢，如果說唯讀檔案，只需要一句RESTORE恢複備份檔案組就可以，不需要任何日誌，需要注意的是在SQL Server2005一下版本中系統中的“維護計劃”還不支援檔案備份的方式。有沒有更細粒度的恢複呢，我們來看下一步的恢複方法。

3、分頁還原

還有一種損壞，比如經典的824頁面讀取錯誤，這種損壞不像前面那麼嚴重。資料庫的每個檔案都能開啟，只是其中的一些頁面壞了。

對於這種情況可以藉助DBCC CHECKDB來進行資料修複。如果DBCC命令無法在不丟資料的前提下修複資料，或者哪怕是允許丟資料庫也修不好資料庫，管理員只能做資料庫完整恢複。但這種情況下，為了少數幾個頁面而恢複整個資料庫，代價就非常昂貴。在SQL Server2005之後，引入了一個分頁還原的功能，可以只還原指定若干頁面，從而能夠大大節省資料庫恢復。可以說是個能夠急救的功能項。

分頁還原用於修複隔離的損壞頁。還原和恢複少了頁面的速度可能比還原一個檔案更快，因此減少了還原作業中處於離線狀態的資料量。然而，如果檔案中要還原的不只是少了頁面而是多數頁面，則還原整個檔案更為有效。例如，如果某個檔案上的大量頁都指出此檔案有未解決的故障，不妨考慮直接還原該檔案。

通常，要進行還原的頁已經由於在訪問頁時遇到錯誤而標記為“可疑”。可疑頁在msdb資料庫的suspect_pages表中進行標識。可以立即還原多個資料庫頁。其過程跟上面的檔案還原過程一樣。在分頁還原後，也要恢複所有的記錄檔備份。每次傳遞日誌重做，向前復原集都會前進一步。

當然這裡面還是有限制的，分頁還原僅可以還原資料頁。頁面不能還原不能用於還原下列內容：

• 交易記錄
• 分配頁：全域分配映射（GAM）頁、共用全域分配映射（SGAM）頁和頁可用空間（PFS）頁。這些系統頁面損壞，分頁還原無法恢複
• 所有資料檔案的頁0（檔案啟動頁）
• 頁1:9（資料啟動頁）
• 全文檢索目錄（Fulltext search catalog）

分頁還原還要符合下列要求：

• 資料庫必須使用完整復原模式。使用大量記錄復原模式時可能不能成功。簡單復原模式無法使用這一功能。
• 唯讀檔案組中的頁面無法還原
• 還原順序必須從完整備份、檔案備份或檔案組備份中恢複頁面開始。所以如果沒有一份資料頁面損壞之前做的的備份，也是無法進行還原
• 分頁還原需要截止到當前記錄檔的連續記錄備份，並且必須恢複所有記錄備份後，頁才能恢複到當前正常狀態。所以如果資料庫曾經曾經做過截斷日誌動作，或者有份記錄備份現在找不到了，那也無法進行頁面恢複。
• Database Backup和分頁還原不能同時進行。

分頁還原的步驟如下：

1、擷取要還原的損壞頁的頁ID。校正和或殘缺寫錯誤將返回頁ID,並提供指定頁所需的資訊。可以通過查詢msdb資料庫裡的suspect_pages表。或者監視事件和SQL Server errorlog檔案裡所報出的錯誤資訊，尋找隨壞頁的頁ID.

select * from msdb..suspect_pages

2、從包含頁的完整Database Backup、檔案備份或檔案組備份開始進行分頁還原。在RESTORE DATABASE 語句中，使用PAGE子句列出所有要還原的頁的頁ID。

PAGE=‘filepage‘

3、應用最近的差異備份。

4、應用後續記錄備份。

5、建立新的資料庫尾記錄備份。

6、還原新的尾日子備份。應用這個新的記錄備份後，就完成了分頁還原，可以開始正常訪問頁面。

比如：我們有個庫，檔案B的檔案ID為1，損壞的頁的ID分別為57、202，而之前這個庫存在B檔案的檔案備份，並且還有兩個記錄備份，我們執行以下指令碼順序還原

RESTORE DATABASE <database> PAGE=‘1:57,1:916‘FROM <file_backup_of_file_B>WITH NORECOVERYGORESTORE LOG  <database>     FROM <log_backup>WITH NORECOVERYGORESTORE LOG  <database>     FROM <log_backup>WITH NORECOVERYGO--這一步很關鍵，一定要備份資料庫尾部日誌BACKUP LOG <database>  TO <new_log_backup>GO--還原最後備份的尾部日誌RESTORE LOG <database>  FROM <new_log_backup> WITH RECOVERYGO

此方法針對824問題是最直接的，也是最快的解決方式，但是在SQL SERVER 2012版本之前不提供介面方式提供，只能使用SQL語句執行。

Database Backup方式選擇，可參考此篇文章：http://www.cnblogs.com/zhijianliutang/p/4063697.html

《SQL Server企業級平台管理實踐》讀書筆記——關於SQL Server資料庫的還原方式