Database Corruption Challenge - Steve Stedman

Article
09/28/2016

2016年9月13日 PASS
Database Corruption Challenge - Steve Stedman
Presented by Steve Stedman
https://www.youtube.com/watch?v=5oBA5nL0uRc&feature=youtu.be&a

1.備份資料庫，還原至測試機來進行修復工作(確保還原程序遭遇問題時，可以重頭再來修復一次)
2.如果決定用allow data loss，嘗試先找出可能遺失的資料並事先嘗試SELECT INTO保留下來
3.執行之前尋求是否有其他人的意見

定期執行

 osql -E -Q"DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS" -oC:\outputfile.txt
sqlcmd -E -Q"DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS" -o C:\outputfile.txt

PS.WITH NO_INFOMSGS只顯示錯誤訊息，一般的information不顯示

基本流程

1.將有損毀的資料庫備份檔還原到測試機，切換成single_user mode。

 ATLER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK_IMMEDIATELY;

2.取得錯誤訊

 USE [master];
GO
DBCC CHECKDB (AdventureWorks) WITH NO_INFOMSGS;
GO

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

2.取回損毀的資料

(1)如果原資料表還能查詢的狀況

A.透過DBCC CHECKTABLE找出哪一筆或哪幾筆資料損毀

page(1, 280) ->page 280

DBCC TRACEON(3604)
DBCC PAGE(dbname, 1, 280,2) with no_infomsgs;
從結果找出m_type
m_type=1 ->data page
slotcount =27 -> number of rows in this page
所以如果執行allow data loss則會出現27然後修復完成(損毀的地方)

B.透過select with non-clustered Index取回一些資料。

select col1, col2 from table with (index=clusted_index1);

利用彙總函數，比對clustered index與non-clustered index資料是否有不同

PS.數值與日期欄位可用SUM(column)，文字欄位可用 SUM(LEN(column))

若查出來值不同(例如col4不同)，表示其中一個欄位有損毀

使用OUTER ALL JOIN 同一個TABLE 找出哪一筆的col4是NULL

因無法直接update or delete那一筆損毀的資料，若執行會出現錯誤

所以從non-cluster index SELECT資料到table_save暫存資料表

D.嘗試修復

DBCC CHECKTABLE(tableName, REPAIR_REBUILD)

通常無法修復

DBCC CHECKTABLE(tableName, REPAIR_ALLOW_DATA_LOSS)

出現找到錯誤，並已經修復
但執行select * from table發現只剩一半的資料，其他消失

E.將資料從table_save補回原資料表

接下就INSERT table select * from table_save
where table.id not in (select id from table)

(2)如果原資料表不能查詢的狀況

A.先透過select with non-clustered Index取回一些資料。(如果後面的步驟無法取得完整資料，最少還有這些)

B.嘗試透過DBCC IND, DBCC PAGE來取回損毀資料表(clustered index)的完整資料

C.再用TRUNCATE TABLE來移除損毀

D.INSERT SELECT from救回資料的資料表

3.修復資料庫並回存資料

truncate table Table_1
insert into Table_1 select * from Table_copy

Database Corruption Challenge - Steve Stedman

Additional resources