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的问题）。