SQL SERVER Undelete 可能性探索（一）Clustered Table

SQLSERVER中，如果數(shù)據(jù)被誤刪了，依照官方的說法，SQLSERVER是沒有undelete的。要想救回被刪除的數(shù)據(jù)，最典型的方法就是透過將備份檔還原成另一DB，將數(shù)據(jù)從另一DB找回來后，再新增回正式的DB。

不過，如果對于SQLSERVER的transaction log結(jié)構(gòu)了解的人，只要交易紀錄還在，就可以透過解析交易紀錄來取得被刪除的數(shù)據(jù)，再將數(shù)據(jù)Insert回DB。只是transaction log的解析有點復(fù)雜。這也是個值得探討且有趣的主題。如果可以，或許可以找時間來談?wù)勥@個。

其實還有一種方法….

SQLSERVER在執(zhí)行delete的時候，它并不是真正立即將數(shù)據(jù)從page中抹除。它只是先將它「標記」為刪除，這時候從邏輯上來看是已刪除了（查不到了），但實際上數(shù)據(jù)還存在，這時我們稱此筆紀錄為 Ghost record。

注：

SQLSERVER的「標記」刪除，在clustered table及heap table標記方法是不一樣的。

真正的將數(shù)據(jù)清除（purge），是由GhostCleanuptask這支系統(tǒng)線程來做，它大約每5~10秒鐘會被喚醒一次，去真正地清除Ghost record。不過，為避免造成系統(tǒng)忙碌，它每次只會檢查或清除有限數(shù)量的頁面（應(yīng)該是10 pages）。

所以從delete commit，到數(shù)據(jù)真正被清除。中間是有一個緩沖期的。

有了這樣的一個緩沖期，就給了我們一個可以Undelete的機會。

首先，最要緊的是，當(dāng)發(fā)生誤刪的時候，必需盡速執(zhí)行下列指令，停用Ghostcleanuptask。以免資料被真的清除。

dbcc traceon(661,-1)   --暫停Ghostcleanuptask

以下我們做一個簡單的Undelete測試

Clustered Table undelete Testing

建立一個測試數(shù)據(jù)庫，一并建立clustered index及non clustered index，并新增10筆數(shù)據(jù)

create database testghost

go

use testghost

go

create table testtbl(c1 int identity primary key,c2 int ,c3 varchar(10))

go

create index idx1 on testtbl(c2)

go

insert into testtbl values

(1,'aaaa'),(2,'aaaa'),(3,'aaaa'),(4,'aaaa'),(5,'aaaa'),

(6,'aaaa'),(7,'aaaa'),(8,'aaaa'),(9,'aaaa'),(10,'aaaa')

檢查該table的page情況

exec master.dbo.[GetPagRowCount] 'testghost','testtbl',-1

注：GetPagRowCount是我自寫的proc，只是方便查閱各page的數(shù)據(jù)，也可以用dbcc page去查.

接著刪除 c1=5 的數(shù)據(jù)

delete from testtbl where c1=5

執(zhí)行select查詢，已經(jīng)查不到數(shù)據(jù)了

假設(shè)C1=5為誤刪數(shù)據(jù)，我們開始做undelete...

暫停  Ghost cleanup task

dbcc traceon(661,-1)

使用procedure去檢查testtbl的page信息?？梢钥吹?clustered頁面跟index頁面有Ghost record產(chǎn)生了。由于這里只有一筆Ghsot record，所以我們幾乎可以確定被誤刪的數(shù)據(jù)存放的位置為pageid 78，這也是要undelete的目標頁面。（這樣的定位方式，可能會有不準確的問題，最好的方法是透過fn_dblog()去取得誤刪的PageID，再用這個procedure，兩者結(jié)果比對，就不會錯了）

PageID確定后，接下來要確定出該筆數(shù)據(jù)所在的SlotID，

我們透過dbcc page，看它的實體紀錄，從record_type可以看出來。Slot 4是Ghost record

至此我們可以確定 PageID 78,SlotID 4，是我們要Undelete的目標。

這是一個 Clustered table，它的標記刪除的方法，是在那筆Row 的第一個byte中加入識別的bits，以標記是Ghost record（heap table則不一樣）。

------------------------------------------------------------------------------------------------

Row的第一個Byte，由0開始從右邊數(shù)來第1~3的位，換算成十進制，代表的意義如下：

0（data record）

1（Forwarded record）

2（a forwarding stub）

3（Index record）

4（blob fragment or row overflow data）

5（ghost index record）

6（ghost data record）

7（ghost version record）

-------------------------------------------------------------------------------------------------

最后利用二進制編輯器，去找到DB的PageID 78,SlotID 4，將第一個byte的Ghost record識別位（十進制6），改成正常的數(shù)據(jù)位（十進制0）。

改完之后，再次執(zhí)行select 查詢，資料已經(jīng)可以查詢的到了...

我再檢查page信息，發(fā)現(xiàn)它仍然被標示成Ghost record

重建所有index...

alter index ALL on testtbl rebuild

再檢查page信息，已經(jīng)正常了

不過，這樣的改法會造成系統(tǒng)基底表的紀錄跟data page的紀錄不符，因此在執(zhí)行dbcc checktable時會有錯誤。

整個Clustered Table Undelete的過程，最后必需執(zhí)行

dbcc checktable(testtbl,repair_allow_data_loss)

修復(fù)數(shù)據(jù)表，雖然是用repair_allow_data_loss但它不會造成數(shù)據(jù)漏失。

最后別忘了將flag 661關(guān)閉

dbcc traceoff(661,-1)

SQL SERVER Undelete 測試成功~~

以上的測試，主要在探索Undelete的可能性，這個測試證明了Undelete是可能的。不過要真正運用在OLTP的作業(yè)環(huán)境上，還有一段路要走。（異動量大、快速定位目標pageid/slotid、Downtime的問題）。