SQL Server 簡單模式下，誤刪除堆表記錄如何恢複（繞過頁首校正）

　首先，我需要強調下，這篇主旨是揭示堆表的刪除記錄找回的原理，我所考慮的方面並不適用於每個人的每種情況，望大家見諒~

　　很多朋友認為資料庫在簡單模式下，堆表誤刪除一條記錄，是無法找回的，因為沒有日誌記錄。其實不然，某種意義上是可以找回的，因為堆表在刪除記錄時，沒有回收空頁面的前提下，只更改了行位移，實際資料沒有被物理刪除，所以利用這點，測試了下恢複資料，果然成功了，但是還有點問題沒有研究出結果：如果不關閉頁面校正，除了更改位移量，刪除資料時還需要更改頁首，這點還沒時間去琢磨，所以恢複資料時還要能推斷出頁首的16進位對應關係，有興趣的朋友可以分享下經驗給我。這裡為了排除頁首的校正錯誤，關閉後測試

廢話不多說，測試的demo如下：

測試環境：

　　SQL Server 2008 R2

　　資料庫：repl_test 簡單模式

　　測試表：test_del

 

測試步驟

1.建立測試表test_del，並插入測試資料。

create table test_del( a int identity,b char(10))goinsert into test_del select 'row 1';insert into test_del select 'row 2';insert into test_del select 'row 3';insert into test_del select 'row 4';insert into test_del select 'row 5';go

2.查看測試資料，顯示正常。

3.DBCC IND命令來找到資料頁id，找到資料頁id：219，這個資料頁存放了test_del的資料

使用dbcc page查看資料頁的內容以及行位移量

dbcc page(repl_test,1,219,1)go

輸出結果為：

DATA:

Slot 0, Offset 0x60, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @0x00000000120CC060

0000000000000000: 10001200 01000000 726f7720 31202020 †........row 1
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...

Slot 1, Offset 0x75, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @0x00000000120CC075

0000000000000000: 10001200 02000000 726f7720 32202020 †........row 2
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...

Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @0x00000000120CC08A

0000000000000000: 10001200 03000000 726f7720 33202020 †........row 3
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...

Slot 3, Offset 0x9f, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @0x00000000120CC09F

0000000000000000: 10001200 04000000 726f7720 34202020 †........row 4
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...

Slot 4, Offset 0xb4, Length 21, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21

Memory Dump @0x00000000120CC0B4

0000000000000000: 10001200 05000000 726f7720 35202020 †........row 5
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...

OFFSET TABLE:

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

其中行位移量第一行為96 (0x60)，實際記錄為row 1，row 2： (0x75)，row 3： (0x8a)，row 4：(0x9f)，row 5： (0xb4)

 

4. 刪除第三行資料 a = 3，b = row 3的記錄

delete test_del where a = 3go

說明a=3 b=row3的記錄已經被刪除。

5.再次查看資料頁的行位移 

dbcc page(repl_test,1,219,1)go

Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 0 (0x0)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)

發現第3行的行位移量被更改成了0，繼續執行

dbcc page(repl_test,1,219,2)go

DATA:

..

00000000120CC060: 10001200 01000000 726f7720 31202020 †........row 1
00000000120CC070: 20200200 00100012 00020000 00726f77 † ...........row
00000000120CC080: 20322020 20202002 00001000 12000300 † 2 .........
00000000120CC090: 0000726f 77203320 20202020 02000010 †..row 3 ....
00000000120CC0A0: 00120004 00000072 6f772034 20202020 †.......row 4
00000000120CC0B0: 20020000 10001200 05000000 726f7720 † ...........row
00000000120CC0C0: 35202020 20200200 00000021 21212121

發現row3的記錄還存在資料頁中！

那麼猜想，是否將第三行的行位移量0x0修改回原來的0x8a就可以恢複記錄了？

利用winHex工具，開啟mdf檔案，因為是219頁面，8*220 = 1802240位元組，所以219的行位移量應該在1802239處，剩下的工作就很簡單了

 

 

6.關閉資料庫的資料頁I/O保護機制，即設定page_verify資料庫選項為none，並將repl_test 資料庫設定為離線，利用winhex找到repl_test.mdf檔案的1802240結尾處16進位碼

 

alter database repl_test set page_verify nonegouse master alter database repl_test set offlinego

 

把repl_test資料庫設定為離線，用winhex工具找到219頁面的結尾處（220頁面的其實位置）：

 

果然第3行的行位移量為00 00，那麼我將其改回8A 00後儲存，並將資料庫設定為online

 

記錄被成功恢複。

如果不進行

alter database repl_test set page_verify none
go

則會讀取表時發生頁面校正錯誤。

那麼如何找回記錄又可以DBCC checkdb安全通過呢？

1.笨方法找回記錄後將原表刪除，損壞頁面會被丟失，重新表，匯入資料即可。

2.修改頁首校正，可惜小弟不才，還沒研究頁首結構對應的物理16進位關係。只靠修改前的頁首，修改後按照還原頁首，這裡無法向大家說明白修改的地方。希望有經驗或者有興趣的朋友可以和我分享下，謝謝~

如何釋放堆中的空閑頁面？

  若要刪除堆中的行並釋放頁，我們可以使用下列方法之一。

• 在 DELETE 語句中指定 TABLOCK 提示。使用 TABLOCK 提示會導致刪除操作擷取表的共用鎖定，而不是行鎖或頁鎖。這將允許釋放頁。

• 如果要從表中刪除所有行，請使用 TRUNCATE TABLE。
• 刪除行之前，請對堆建立叢集索引。刪除行之後，可以刪除叢集索引。與先前的方法相比，此方法非常耗時，並且使用更多的臨時資源。

如果釋放空閑頁面空間，很有可能記錄就無法再恢複了，同時說明資料庫完整模式+記錄備份是多麼的重要，可以省去很多發雜的步驟

文筆不好，如果哪裡看的模糊請留言。