DBCC CHECKDB搭配REPAIR_ALLOW_DATA_LOSS修復資料庫的資料遺失風險

2016-01-07

2142
0
[SQL]災難復原
2016-01-08

相信很多人都知道DBCC CHECKDB('DB Name',REPAIR_ALLOW_DATA_LOSS)

語法可以幫忙修復有問題的資料庫。而MSDN也有說明這個動作會造成資料遺失，

建議不要用這種方式修復資料庫。

近日看見一部利用Restore Page方式修復資料庫的影片，想想如果用DBCC CHECKDB

來修會造成多少資料的損失呢?

以下的Code會先幫我們將環境建立起來。

大約簡單的說一下這一次LAB的內容，就是建立兩張資料表tb1及tb2，tb2去關聯tb1。

然後在tb1塞入1000筆資料，在tb2就塞入一筆資料關聯tb1的ID=1的那一筆資料即可。

然後我們再破壞tb1實體檔案Page，最後再用DBCC CHECKDB修復資料庫。

Use [master]
GO
--建立資料庫DB1
Create Database [DB1]
GO

Use [DB1]
GO
--建立資料表tb1
Create Table tb1(ID Int Identity Primary Key,Name Char(6))
GO
--建立資料表tb2
Create Table tb2(ID Int,Phone Char(10))
GO
--讓資料表tb2關聯至資料表tb1
Alter Table tb2 Add Constraint [FK_tb1] Foreign Key(ID) References tb1(ID)
GO
--先做一次完整備份
Backup Database [DB1] To Disk='E:\SQLBCK\DB1.bak' With Compression
GO
--寫入1000筆資料進tb1
Insert Into tb1 values('Rock')
GO 1000
--寫入一筆資料進tb2
Insert Into tb2 values(1,'0911123123')
GO
--先做一次交易紀錄備份
Backup Log [DB1] To Disk='E:\SQLBCK\DB1_log.bak' With Compression
GO

下圖是我用上面Code建置環境

建置完成後我先測試tb1及tb2的關聯是否正常，由下圖可以看到當時tb2有資料關聯tb1的

ID=1的資料。因此當我要刪除tb1 ID=1的資料時發生了失敗。

接下來我們用sys.dm_db_database_page_allocations這一個DMF來找出tb1所在的Page ID，

由下圖紅色圈選處我們可以看見tb1的Page ID是121,142,144等三個Page。等等我們就來竄

改一下121這一個Page的內容，來讓資料庫產生錯誤。

要修改mdf，我們得先將DB1資料庫設成OFFLINE。否則SQL已佔用該mdf，我們是無法異動

該mdf檔案。

接下來我們利用XVI32這一套軟體來修改DB1的mdf檔案，下圖紅色圈選處991232就是Page ID

121的實體位址(算法就是8192 X 121 = 991232)，這樣XVI32就幫我們定位到Page ID 121的位置。

下面兩張圖就是我就隨便挑個Byte並修改該Byte的內容。

修改完mdf檔後，我們再讓DB1資料庫上線。

此時我們執行DBCC CHECKDB，如下圖所示，果然SQL報錯了。錯誤內容中都描述了121 Page

是有問題的。

這時後我們去Select tb1資料表，SQL也是報出824的錯誤。

此錯誤表示 Windows 回報從磁碟成功讀取頁面，但 SQL Server 發現頁面錯誤。此錯誤與錯誤 823 類似，不同的是 Windows 並未偵測到此錯誤。這通常表示 I/O 子系統發生問題，例如磁碟機故障、磁碟韌體問題、錯誤的裝置驅動程式等。

這時我們直接用 DBCC CHECKDB('DB1',REPAIR_ALLOW_DATA_LOSE)來修復資料庫。

如下圖所示，SQL顯示修復了資料庫，但從訊息看出tb1在修復後只剩下574筆資料，整整少

掉了426筆資料。

完成修復後我們再一次Select tb1，(如下圖)果然ID是從427開始，427前的所有資料都被

刪掉了。

而在一次用sys.dm_db_database_page_allocations這一個DMF來檢視tb1的Page狀況，

修復後tb1的Page只剩2個(原來是3個)，且Page ID也都不一樣了。

還記得我們之前設定tb2關聯tb1嗎 ? tb2中我們有寫入一筆資料去關連到tb1的ID=1的資料。

而tb1中ID=1的那一筆資料已經在SQL修復中被刪掉了，但SQL並不會因關聯問題也一併

刪除tb2的資料。所以修復資料庫後的資料關聯也是有問題的。此時我們用

DBCC CHECKCONSTRAINTS來檢查一下Constraint(如下圖)。

我們可以看見tb2中有一筆紀錄有Constraint不正確的問題。

MSDN寫到 : 修復作業並不考慮資料表或資料表之間的任何條件約束。如果指定的資料表涉及一或多項條件約束，建議您在修復作業之後，執行 DBCC CHECKCONSTRAINTS。

MSDN建議 : 最好不要使用這些 REPAIR 選項。若要修復錯誤，我們建議您從備份中還原。如果您必須使用 REPAIR，請執行不含修復選項的 DBCC CHECKDB 來尋找要使用的修復層級。如果您使用 REPAIR_ALLOW_DATA_LOSS 層級，建議您在搭配這個選項執行 DBCC CHECKDB 之前，先備份資料庫。

由上述簡易實驗後，日後我們要修復資料庫錯誤千萬別第一時間就用DBCC CHECKDB

來做，因為你無法知道SQL的修復會給你帶來哪一些驚喜啊。

參考資料來源 : DBCC CHECKDB (Transact-SQL)