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

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

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

其實還有一種方法….

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

注：

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

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

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

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

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

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

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

Clustered Table undelete Testing

建立一個測試數據庫，一并建立clustered index及non clustered index，并新增10筆數據

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的數據，也可以用dbcc page去查.

接著刪除 c1=5 的數據

delete from testtbl where c1=5

執行select查詢，已經查不到數據了

假設C1=5為誤刪數據，我們開始做undelete...

暫停  Ghost cleanup task

dbcc traceon(661,-1)

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

PageID確定后，接下來要確定出該筆數據所在的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開始從右邊數來第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），改成正常的數據位（十進制0）。

改完之后，再次執行select 查詢，資料已經可以查詢的到了...

我再檢查page信息，發現它仍然被標示成Ghost record

重建所有index...

alter index ALL on testtbl rebuild

再檢查page信息，已經正常了

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

整個Clustered Table Undelete的過程，最后必需執行

dbcc checktable(testtbl,repair_allow_data_loss)

修復數據表，雖然是用repair_allow_data_loss但它不會造成數據漏失。

最后別忘了將flag 661關閉

dbcc traceoff(661,-1)

SQL SERVER Undelete 測試成功~~

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