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

【魄爺】

　　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>　　go

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