~楓花雪岳~: [SQL] 分頁還原

紀錄當 DB 毀損時，利用分頁還原的步驟，利用 AdventureWorks2014 的 HumanResources.Employee Table 當成練習目標

事先準備

破壞 DB 方式，請參考這篇文章 [SQL] 模擬資料庫毀損

-- AdventureWorks2014 預設是簡單模式，分頁還原無法在該模式下進行，
-- 因此轉為完整模式
ALTER DATABASE AdventureWorks2014 SET Recovery FULL

-- 要進行分頁還原前，當然要有備份檔案存在
BACKUP DATABASE AdventureWorks2014 TO DISK = N'D:\PageRestoreDemo.bak' 
BACKUP LOG AdventureWorks2014 TO DISK = N'D:\PageRestoreDemo.bak' 

-- 對 DB 進行破壞，請參考 [SQL] 模擬資料庫毀損 該篇文章
-- 使用 DBCC WRITEPAGE 必須是單一使用者模式
ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- 毀損 PK_Employee_BusinessEntityID 叢集索引，所在的 Page 733 => Data Page
DBCC WRITEPAGE('AdventureWorks2014', 1, 733, 60, 1, 0x00, 1)
-- 切回多人模式
ALTER DATABASE AdventureWorks2014 SET multi_user WITH ROLLBACK IMMEDIATE

分頁還原

當執行 T-SQL 語法時，會發現 DB 毀損

SELECT * FROM HumanResources.Employee

訊息 824，層級 24，狀態 2，行 23
SQL Server 偵測到邏輯的一致性 I/O 錯誤: 不正確的總和檢查碼 (預期: 0x9d61a100; 實際: 0x9d61a18b)。這是當在檔案 'D:\AdventureWorks2014.mdf' 中位移 0x000000005ba000 的資料庫識別碼 7 之頁面 (1:733) 進行讀取的期間所發生的。SQL Server 錯誤記錄檔和系統事件記錄檔中的訊息，或許可以提供其他詳細資訊。這是嚴重的錯誤狀況，且可能會損及資料庫的完整性，所以必須立即更正。請執行完整的資料庫一致性檢查 (DBCC CHECKDB)。會導致這個錯誤的原因有許多可能性; 如需詳細資訊，請參閱《SQL Server 線上叢書》。

執行 DBCC

DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;
GO

訊息 8939，層級 16，狀態 98，行 33
資料表錯誤: 物件識別碼 1237579447，索引識別碼 1，分割區識別碼 72057594046644224，配置單位識別碼 72057594053197824 (類型 In-row data)，頁面 (1:733)。測試 (IS_OFF (BUF_IOERR, pBUF->bstat)) 失敗。值為 133129 和 -4。
訊息 8928，層級 16，狀態 1，行 33
物件識別碼 1237579447，索引識別碼 1，分割區識別碼 72057594046644224，配置單位識別碼 72057594053197824 (類型 In-row data): 頁面 (1:733) 無法處理。請參閱其他錯誤以取得詳細資料。
CHECKTABLE 發現了資料表 'HumanResources.Employee' (物件識別碼 1237579447) 中 0 個配置錯誤和 2 個一致性錯誤。
repair_allow_data_loss 是 DBCC CHECKTABLE (AdventureWorks2014.HumanResources.Employee) 所發現之錯誤的最小修復層級。
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。

執行 DBCC CheckDB 或 DBCC CheckTable 後，有發現毀損 Page 的話，會記錄在 msdb.dbo.suspect_pages Table 內，event_type = 2 代表總和檢查碼錯誤，該練習只破壞一個 Page 所以只會有一筆資料

SELECT * FROM msdb.dbo.suspect_pages

進行結尾備份，並確認 bak 內有幾個備份檔案

USE MASTER
GO
BACKUP LOG AdventureWorks2014 TO DISK = N'D:\PageRestoreDemo.bak'  WITH NORECOVERY

RESTORE HEADERONLY FROM DISK = N'D:\PageRestoreDemo.bak'

BackupType：1 代表完整備份（Full Backup）、2 代表交易備份（Log Backup）
Position：代表 bak 內的備份檔案，為 restore 還原語法的 File 參數數值

開始進行還原

-- 利用完整備份還原，並指定還原 Page
RESTORE DATABASE AdventureWorks2014 
Page = '1:733' 
FROM DISK = N'D:\PageRestoreDemo.bak' 
WITH 
    File = 1 ,
    NORECOVERY ,
    stats = 10

-- 依序還原全部 Log Backup
RESTORE DATABASE AdventureWorks2014
FROM DISK = N'D:\PageRestoreDemo.bak' 
WITH 
    File = 2 ,
    NORECOVERY ,
    stats = 10

RESTORE DATABASE AdventureWorks2014
FROM DISK = N'D:\PageRestoreDemo.bak' 
WITH 
    File = 3 ,
    RECOVERY ,
    stats = 10

再次執行 DBCC CheckTable

DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;
GO

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。

檢查 suspect_pages Table 會發現 event_type = 4 表示"已還原 (在頁面標示為不正確之後還原頁面)"

SELECT * FROM msdb.dbo.suspect_pages

其他注意事項

一次還原多個 Page

RESTORE DATABASE AdventureWorks2014 
Page = '1:733,1:5133' 
FROM DISK = N'D:\PageRestoreDemo.bak' 
WITH 
    File = 1 ,
    NORECOVERY ,
    stats = 10

還原時一定要把全部的 Log Backup 都還原回去

假如只還原完整備份就 recovery，DB 會顯示正在還原，DB 會無法使用，並有下列提醒訊息

已處理資料庫 'AdventureWorks2014' 的 1 頁，檔案 1 上的檔案 'AdventureWorks2014_Data'。
已處理資料庫 'AdventureWorks2014' 的 2 頁，檔案 1 上的檔案 'AdventureWorks2014_Log'。
向前復原目前的起始點位於記錄序號 (LSN) 45000000069400001。必須有超過 LSN 45000000075900001 的其他向前復原，才能完成還原順序。
這個 RESTORE 陳述式成功執行了一些動作，但是由於需要一個或多個 RESTORE 步驟，因此無法讓資料庫上線。先前的訊息指出為何無法在此時復原的原因。
RESTORE DATABASE ... FILE= 已於 1.900 秒內成功處理了 3 頁 (0.008 MB/sec)。

假如沒有還原全部的 Log Bakcup 就 recovery，會出現下面錯誤訊息

訊息 4318，層級 16，狀態 0，行 61
檔案 'AdventureWorks2014_Data' 已向前復原至 LSN 45000000075900001。這個記錄檔結束於 LSN 45000000070300001，若要套用 WITH RECOVERY 選項則還太早。請重新發出 RESTORE LOG 陳述式 WITH NORECOVERY。
訊息 3119，層級 16，狀態 1，行 61
計劃 RESTORE 陳述式的時候識別出問題。先前的訊息可提供詳細資料。
訊息 3013，層級 16，狀態 1，行 61
RESTORE DATABASE 正在異常結束。