恢複SQL Server被誤删除的資料

《恢複SQL Server被誤删除的資料（再擴充）》

位址：http://www.cnblogs.com/lyhabc/p/4620764.html

曾經想實作Log Explorer for SQL Server的功能，利用ldf裡面的日志來還原誤删除的資料

這裡有一篇文章做到了，不過似乎不是所有的資料類型都支援

以下為譯文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

在我使用SQLSERVER的這些年裡面，大部分人都會問我一個問題：“能不能恢複被删除的資料？？”

現在，從SQLSERVER2005 或以上版本能很容易能夠恢複被删除的資料

（注意：這個腳本能恢複下面的資料類型的資料 而且相容CS 排序規則）

image

text

uniqueidentifier

tinyint

smallint

int

smalldatetime

real

money

datetime

float

sql_variant

ntext

bit

decimal

numeric

smallmoney

bigint

varbinary

varchar

binary

char

timestamp

nvarchar

nchar

xml

sysname

讓我來用demo來解釋一下我是怎麼做到的

現在你需要建立一個存儲過程來恢複你的資料

View Code

恢複你的資料

執行了下面的存儲過程之後你會發現會顯示出剛才删除的資料

解釋

究竟他是如何工作的?讓我們來一步一步來,這個過程涉及到7個步驟:

步驟1:

我們需要獲得SQLSERVER删除的資料記錄.使用标準SQLSERVER函數fn_dblog，我們能夠容易的獲得事務日志記錄（包括

已删的資料。不過，我們隻需要事務日志中選中的被删資料，是以我們的過濾條件需要包含3個字段 Context, Operation & AllocUnitName）

We need to get the deleted records from sql server. By using the standard SQL Server function fn_blog, we can easily get all transaction log (Including deleted data. But, we need only the selected deleted records from the transaction log. So we included three filters (Context, Operation , AllocUnitName).

Context (‘LCX_MARK_AS_GHOST’and ‘LCX_HEAP’)

Operation (‘LOP_DELETE_ROWS’)

AllocUnitName(‘dbo.aa’) –- Schema + table Name

Context可以說明是堆表還是聚集表

Operation：删除操作

AllocUnitName：配置設定單元名稱，表名

下面是一個代碼片段

這個查詢會傳回不同列的資訊，但是我們隻需要選擇[RowLog Contents 0]列，去獲得被删除的資料的内容

RowLog content 0列的内容類似于這樣

“0x300018000100000000000000006B0000564920205900000

00500E001002800426F62206A65727279″

步驟2:

現在，我們已經删除了資料，這些資料以hex碼的形式放在事務日志裡，這些hex碼是有規律的，我們根據這些規律可以很容易恢複這些資料。

不過在恢複這些資料之前，我們需要了解這些格式。這些格式在KalenDelaney’s SQL Internal’s book.的書裡面有講解

1 Byte : Status Bit A

1 Byte : Status Bit B

2 Bytes : Fixed length size

n Bytes : Fixed length data

2 Bytes : Total Number of Columns

n Bytes : NULL Bitmap (1 bit for each column as 1 indicates that the column is null and 0 indicate that the column is not null)

2 Bytes : Number of variable-length columns

n Bytes : Column offset array (2x variable length column)

n Bytes : Data for variable length columns

是以, hex碼的“RowLog content 0″列的内容就等價于

“Status Bit A +Status Bit B +Fixed length size +Fixed length data +Total Number of Columns +NULL Bitmap +Number of variable-length columns +NULL Bitmap+Number of variable-length columns +Column offset array +Data for variable length columns.”

更詳細的可以參考：SQL Server2008存儲結構之堆表、行溢出

關于資料行的結構我們還可以采用稍微宏觀一些的視角來檢視。

　　

步驟3:

現在，我們需要解剖RowLog Content o列的内容（我們删除的資料的Hex碼），利用上面的資料行的結構

[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+Status Bit B + 1,2 bytes)

[Total No of Columns]= Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)

[Null Bitmap length] = Ceiling ([Total No of Columns]/8.0)

[Null Bytes]= Substring (RowLog content 0, Status Bit A+ Status Bit B +[Fixed Length Data] +1, [Null Bitmap length] )

Total no of variable columns = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )

Column Offset Array= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , Total no of variable columns*2 )

Variable Column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+( Total no of variable columns*2)

步驟4:

現在我們已經将hex碼切開了(0x300008000100000002000001001300604F7D59),是以,我們能找到删除的行的某列的資料是否為null值

根據NULL位圖。為了完成将NULL Bytes的hex碼轉換為二進制格式（正如之前讨論的，1表示行中對應的那一列為null，而0則表示對應的列有實際的資料）

如果還不是明白的童鞋可以看一下我寫的這篇文章：《SQLSERVER中NULL位圖的作用》

步驟5:

現在，我們已經做了初步的資料分割 (Step-3) 和null值判斷(Step-4) 。然後我們需要使用代碼片段去獲得列資料，例如：列名，列大小，精度，範圍

和最重要的葉子的null位（確定列資料是固定長度的（<=-1表示可變長度）或者固定長度的（>=1））

使用下面的SQL語句

與(Step-1,2,3,4) 獲得的資料表做join連接配接，根據allocunits.[Allocation_Unit_Id]。

現在我們知道表和表中的資料資訊，那麼我們需要利用這些資料去将 [RowLog Contents 0] 列裡的hex碼的資料插入到表中的相應列

現在我們需要關心每一列的資料究竟是固定長度的還是可變長度的

步驟6:

我們收集了每列的hex格式的資料。現在我們需要利用[System_type_id]去轉換這些資料回去正确的資料類型

每一種資料類型都有不同的資料類型轉換機制。

步驟7:

最終我們做一個資料透視表，你會看到最後的結果：被删的資料回來了！

注意：這些資料隻是展示出來并沒有自動插入回表中，你需要将這些資料重新插入回去表中！

我的測試

經過測試，作者寫的這個存儲過程還是有些問題

如果你建立的測試表的資料類型有xml或者是一些text資料類型的字段會有報錯

但是一般的資料類型則不會，例如nvarchar這些

還有不要在存儲過程的最後加

否則會報錯

總結

實際上這個存儲過程還是挺有研究意義的，對于想做一個跟Log Explorer for SQL Server軟體功能差不多的軟體出來

還是有可能的，跟着作者的思路，一步一步實作

苦于最近太忙，先分享出來，以後再研究這個存儲過程了~

如有不對的地方，歡迎大家拍磚o(∩_∩)o 

支援date類型 感謝園友 dwchaoyue

2015-12-17 補充

注意：腳本中大量使用了reverse函數來進行轉換，因為reverse函數的定義是傳回字元串值的逆序，不是位元組的逆序

那麼就會導緻有些資料類型是按位元組逆序的，就會出錯

在SQL Server中，sys.fn_PhysLocFormatter這個函數也是使用reverse函數進行RID逆序，當遇到81-FE之間的位元組時 被認為是雙位元組字元組合在一起參與逆序運算

是以得出來的某些結果也是錯的，詳細請參考《SQL Server性能調優實戰》 P115

函數的邏輯是，每個位元組加上相應的位數

比如 2這個數字是在第二個位元組位置的，那麼公式就是2*POWER(2,8) =512

相應進制

二進制後面加8個0

十六進制後面加2個0

隻要學過計算機基礎都不難了解的

本文版權歸作者所有，未經作者同意不得轉載。