恢复SQLSERVER被误删除的数据

发布时间：2020-12-12 13:19:07 所属栏目：MsSql教程来源：网络整理

导读：曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据这里有一篇文章做到了，不过似乎不是所有的数据类型都支持以下为译文： http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ 在我使用SQLSERVE

曾经想实现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来解释一下我是怎么做到的

USE masterGO--创建数据库CREATEDATABASE testGOUSE[test]GO--创建表CREATETABLE[dbo].[aa]( ? ?[id][int]IDENTITY(1,1) NOTNULL,? ?[NAME][nvarchar](200) NULL) ON[PRIMARY]GO--插入测试数据INSERT[dbo].[aa] ? ? ? ?( [NAME] )SELECT'你好'GO--删除数据Deletefrom aaGo--验证数据是否已经删除Select*from aaGo

现在你需要创建一个存储过程来恢复你的数据

View Code

恢复你的数据

--恢复数据，不加时间段条件 ?参数：数据库名，表名--EXAMPLE #1 : FOR ALL DELETED RECORDSEXEC Recover_Deleted_Data_Proc 'test','dbo.aa'GO--恢复数据，加时间段条件--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGEEXEC Recover_Deleted_Data_Proc 'test','dbo.aa','2014-04-23','2014-04-23'

执行了下面的存储过程之后你会发现会显示出刚才删除的数据

EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'GO

解释

究竟他是如何工作的?让我们来一步一步来,这个过程涉及到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：分配单元名称，表名

下面是一个代码片段

SELECT[RowLog Contents 0]FROM    sys.fn_dblog(NULL,NULL)WHERE   AllocUnitName ='dbo.aa'AND Context IN ( 'LCX_MARK_AS_GHOST','LCX_HEAP' ) ? ? ? ?AND Operation IN ( 'LOP_DELETE_ROWS' )

这个查询会返回不同列的信息，但是我们只需要选择[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语句

SELECT*FROM ? ?sys.allocation_units allocunits ? ? ? ?INNERJOIN sys.partitions partitions ON ( allocunits.type IN ( 1,3 ) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AND partitions.hobt_id = allocunits.container_id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?OR ( allocunits.type =2AND partitions.partition_id = allocunits.container_id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ) ? ? ? ?INNERJOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id ? ? ? ?LEFTOUTERJOIN syscolumns ON syscolumns.id = partitions.object_idAND syscolumns.colid = cols.partition_column_id

与(Step-1,2,3,4) 获得的数据表做join连接，根据allocunits.[Allocation_Unit_Id]。

现在我们知道表和表中的数据信息，那么我们需要利用这些数据去将 [RowLog Contents 0] 列里的hex码的数据插入到表中的相应列

现在我们需要关心每一列的数据究竟是固定长度的还是可变长度的

步骤6:

我们收集了每列的hex格式的数据。现在我们需要利用[System_type_id]去转换这些数据回去正确的数据类型

每一种数据类型都有不同的数据类型转换机制。

--NVARCHAR,NCHARWHEN system_type_id IN (231,239) THENLTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --VARCHAR,CHARWHEN system_type_id IN (167,175) THENLTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value,0x00,0x20)))) --TINY INTEGERWHEN system_type_id =48THENCONVERT(VARCHAR(MAX),CONVERT(TINYINT,CONVERT(BINARY(1),REVERSE (hex_Value)))) --SMALL INTEGERWHEN system_type_id =52THENCONVERT(VARCHAR(MAX),CONVERT(SMALLINT,CONVERT(BINARY(2),REVERSE (hex_Value)))) -- INTEGERWHEN system_type_id =56THENCONVERT(VARCHAR(MAX),CONVERT(INT,CONVERT(BINARY(4),REVERSE(hex_Value)))) -- BIG INTEGERWHEN system_type_id =127THENCONVERT(VARCHAR(MAX),CONVERT(BIGINT,CONVERT(BINARY(8),REVERSE(hex_Value)))) --DATETIMEWHEN system_type_id =61ThenCONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100) --SMALL DATETIMEWHEN system_type_id =58ThenCONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME--- NUMERICWHEN system_type_id =108THENCONVERT(VARCHAR(MAX),CAST(CONVERT(NUMERIC(18,14),CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) asFLOAT)) --MONEY,SMALLMONEYWHEN system_type_id In(60,122) THENCONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2) --- DECIMALWHEN system_type_id =106THENCONVERT(VARCHAR(MAX),CAST(CONVERT(Decimal(38,34),Convert(VARBINARY,0) + hex_Value) asFLOAT)) -- BITWHEN system_type_id =104THENCONVERT(VARCHAR(MAX),CONVERT (BIT,hex_Value)%2)) --- FLOATWHEN system_type_id =62THENRTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) ASBIGINT)) * (1.0+ (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASBIGINT) &0x000FFFFFFFFFFFFF) *POWER(CAST(2ASFLOAT),-52)) *POWER(CAST(2ASFLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASBIGINT) &0x7ff0000000000000) /EXP(52*LOG(2))-1023))),53,LEN(hex_Value)))) --REALWhen  system_type_id =59THENLeft(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) ASBIGINT))* (1.0+ (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASBIGINT) &0x007FFFFF) *POWER(CAST(2ASReal),-23)) *POWER(CAST(2ASReal),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASINT) )&0x7f800000)/EXP(23*LOG(2))-127))ASREAL),23,23)),8) --BINARY,VARBINARYWHEN system_type_id In (165,173) THEN (CASEWHENCharindex(0x,cast(''AS XML).value('xs:hexBinary(sql:column("hex_value"))','varbinary(max)')) =0THEN'0x'ELSE''END) +cast(''AS XML).value('xs:hexBinary(sql:column("hex_value"))','varchar(max)') ?--UNIQUEIDENTIFIER WHEN system_type_id =36THENCONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))

步骤7:

最终我们做一个数据透视表，你会看到最后的结果：被删的数据回来了！

注意：这些数据只是展示出来并没有自动插入回表中，你需要将这些数据重新插入回去表中！

我的测试

经过测试，作者写的这个存储过程还是有些问题

如果你创建的测试表的数据类型有xml或者是一些text数据类型的字段会有报错

Msg 537,Level16,State 3,Procedure Recover_Deleted_Data_Proc,Line 525Invalid length parameter passed to the LEFTorSUBSTRINGfunction.Msg 9420,State 1,Line 651XML parsing: line 1,character2,illegal xml character

但是一般的数据类型则不会，例如nvarchar这些

还有不要在存储过程的最后加

--Recover the deleted data without date rangeEXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'GO--Recover the deleted data it with date rangeEXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table','2012-06-01','2012-06-30'

否则会报错

消息 50000，级别 16，状态 1，过程 Recover_Deleted_Data_Proc，第 290 行There is no data in the logas per the search criteria

摘自：http://www.cnblogs.com/lyhabc/p/3683147.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!