SQL Server2012的分頁資料庫回復

by adonis 24. 四月 2013 15:38

作    者:楊先民
審    稿:張智凱

前言
SQL Server 2005之後開始,SQL Server開始支援進階資料庫備份,包含分頁(page)回復以及piecemeal回復,本期將要介紹分頁回復的機制搭配 SQL Server 2012新的管理工具界面。

何謂分頁回復

一般在資料庫備份回復課程中,微軟會將它區分為「Complete」回復以及「Online」回復兩個主題,資料庫無論怎麼毀損,都可以使用 complete回復將資料救回,然而當資料庫資料很大時,可能就要考慮 online回復的議題了,其中分頁回復就是 online回復中的主題之一。

分頁為何會損壞?主要是資料庫伺服器不正常關機,也許是停電,或是踢到電源供應器,讓正在讀寫的資料不正常的寫入,就好比usb隨身碟,沒有正常退出就拔除一樣,正在寫入的資料將會不正常。

在 SQL Server 2000之前的解決方式,是使用 complete的回復方式,也就是先備份 tail-log,再由完整、差異、交易記錄將資料逐一救回,這樣的缺點是速度慢,而其實要回復的資料很少,正所謂「痛苦時間太多,但收穫太少」(笑)。

所以,在 SQL Server 2005之後,就新増了「分頁回復」的機制,讓管理者不用回復大多數都沒有問題的資料,因而浪費了時間,只要復原壞掉的分頁即可。

不過,如果你想要進行分頁回復的作業,需要先確認幾件事:

1.    資料庫的復原模型必需設定為 Full,而 bulk-logged模型則有條件回復,不可以為 sample模型。
2.    分頁毀損的檔案群組必需是可讀寫的,不能是唯讀。
3.    只能夠回復資料庫分頁(Database page),不能回復 GAM pages、SGAM pages、PFS pages。
4.    所有資料分頁的 page 0無法回復(檔案的 boot page)。
5.    1:9分頁無法回復(此為資料庫的 boot page)。
6.    全文檢索的 catalog。

另外,標準版的 SQL Server只支援 offline 分頁回復,只有企業版的 SQL Server 支援 online分頁回復功能。

開始環境設定

1.    首先先對資料庫做完整資料庫備份,確定資料庫復原模型為 Full。
backup database [AdventureWorks] to disk = 'c:\backups\ads.bak' with format
2.    建立一個資料表,並且備份 log,此資料表為旗標之用。
create table table1 (a int , b int)
backup log [AdventureWorks] to disk ='c:\backups\adslog.bak' with format
3.    利用DBCC CHECKALLOC尋找某個資料表的分頁,準備破壞,以本例而言是 Production.Location這個資料,尋找 index 1的root page,以本例而言,找到的是13830這個分頁。

image 

4.將 SQL Server的服務停止,並且利用微軟所提供的範例檔案 corrupter.exe,將13830這個分頁破壞掉,再將 SQL Server的服務啟動。

image

5.    此時分頁雖然已經被破壞掉,但是系統並沒有發覺,除非使用 DBCC CHECKDB這個指令進行資料庫檢查,不然資料庫並不知有毀壞的分頁。
6.    選擇資料庫右鍵工作→還原→頁面。

image

7.    果然,此時 SQL Server並沒有發覺有毀損的分頁。

image

8.    這時你可以按下「檢查資料庫頁面」,這時系統會開始進行資料庫的毀損檢查,就會發現有壞掉分頁的存在了。

image

9.    可以直接按下「確定」,不過如果有欵問的人可以產生指令碼研究一下:

USE [master]
RESTORE DATABASE [AdventureWorks] PAGE='1:13830' FROM  DISK = N'c:\backups\ads.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

BACKUP LOG [AdventureWorks] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks_LogBackup_2013-04-24_14-35-29.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks_LogBackup_2013-04-24_14-35-29', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 5

RESTORE LOG [AdventureWorks] FROM  DISK = N'c:\backups\adslog.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [AdventureWorks] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks_LogBackup_2013-04-24_14-35-29.bak' WITH  NOUNLOAD,  STATS = 5


10.    這幾個指令中,最先做的是分復毀損的分頁,利用 restore … page=的方式復原毀損的分頁,然後對資料庫進行交易記錄的備份。
11.    再把之前的 log restore,以及一開始備份的分頁記錄回復即可。

以本例而言,要做到 online restore,必需不能做 norecovery的 tail log備份,不然資料庫就會需要把線上使用者趕離線上,所以第一部必需使用 restore…page的方式,直接把完整資料庫中,屬於壞掉分頁的部分回復。

但回復的分頁,時間點是當初完整資料庫備份的時間點,你必需補齊「完整資料庫備份,到分頁毀損之間的交易記錄時間點」。

舉例而言,上午九點完整資料庫備份,九點十分資料庫分頁毀損,你用 restore page指令復原資料分頁,但資料分頁是回復成九點狀態而非九點十分,這十分鐘的差距你就必需足。

怎麼補足?就是把交易備份下來,再把這個交易記錄回復即可,因為此交易記錄就是九點完整備份到目前為止的交易資料。

12.    回復完後,可以看一下msdb資料庫中的suspect_pages資料表,看一下毀損分頁的狀態,如下:

image

event_type為4,代表 restored,表示此分頁已經回復成功。

同場加映


通常在進行這種分頁回復之後,切記最好做一次完整式資料庫備份,必需養成良好的習慣,如果你什麼都不做,再把 SQL Server服務關掉,將分頁再破壞一次,隨即想要再回復分頁,就會出現下面的錯誤訊息:

image

此時的解決方案很簡單,就是做一次交易記錄的備份,即可再進行資料分頁的還原。

結語

本期介紹 SQL Server的進階還原,資料分頁的 online restore,由於 SQL Server 2012的管理工具,提供了圖形介面可以使用,在操作上而言比以往更為方便。

image

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List