恢复SQLSERVER被误删除的数据(转——收藏)

时间:2023-03-08 16:10:50
恢复SQLSERVER被误删除的数据(转——收藏)

恢复SQLSERVER被误删除的数据

摘自:http://www.cnblogs.com/lyhabc/p/3683147.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来解释一下我是怎么做到的

USE master
GO
--创建数据库
CREATE DATABASE test
GO USE [test]
GO --创建表
CREATE TABLE [dbo].[aa](
[id] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](200) NULL
) ON [PRIMARY]
GO --插入测试数据
INSERT [dbo].[aa]
( [NAME] )
SELECT '你好'
GO --删除数据
Delete from aa
Go --验证数据是否已经删除
Select * from aa
Go

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

恢复你的数据

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

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

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

恢复SQLSERVER被误删除的数据(转——收藏)


解释

究竟他是如何工作的?让我们来一步一步来,这个过程涉及到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存储结构之堆表、行溢出

关于数据行的结构我们还可以采用稍微宏观一些的视角来查看。

恢复SQLSERVER被误删除的数据(转——收藏)

  恢复SQLSERVER被误删除的数据(转——收藏)

步骤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)

恢复SQLSERVER被误删除的数据(转——收藏)

步骤4:

现在我们已经将hex码切开了(0x300008000100000002000001001300604F7D59),所以,我们能找到删除的行的某列的数据是否为null值

根据NULL位图。为了完成将NULL Bytes的hex码转换为二进制格式(正如之前讨论的,1表示行中对应的那一列为null,而0则表示对应的列有实际的数据)

如果还不是明白的童鞋可以看一下我写的这篇文章:《SQLSERVER中NULL位图的作用

恢复SQLSERVER被误删除的数据(转——收藏)

步骤5:

现在,我们已经做了初步的数据分割 (Step-3) 和null值判断(Step-4) 。然后我们需要使用代码片段去获得列数据,例如:列名,列大小,精度,范围

和最重要的叶子的null位(确保列数据是固定长度的(<=-1表示固定长度)或者可变长度的(>=1))

使用下面的SQL语句

SELECT  *
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 1, 3 )
AND partitions.hobt_id = allocunits.container_id
)
OR ( allocunits.type = 2
AND partitions.partition_id = allocunits.container_id
)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id
AND syscolumns.colid = cols.partition_column_id

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

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

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

恢复SQLSERVER被误删除的数据(转——收藏)

步骤6:

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

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

--NVARCHAR ,NCHAR
WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --VARCHAR,CHAR
WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20)))) --TINY INTEGER
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) -- INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- BIG INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value)))) --DATETIME
WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100) --SMALL DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME --- NUMERIC
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --MONEY,SMALLMONEY
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2) --- DECIMAL
WHEN system_type_id = 106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), Convert(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) -- BIT
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) --- FLOAT
WHEN system_type_id = 62 THEN RTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --REAL
When system_type_id =59 THEN Left(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --BINARY,VARBINARY
WHEN system_type_id In (165,173) THEN (CASE WHEN Charindex(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varbinary(max)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))', 'varchar(max)') --UNIQUEIDENTIFIER
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))

恢复SQLSERVER被误删除的数据(转——收藏)

步骤7:

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

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

恢复SQLSERVER被误删除的数据(转——收藏)


我的测试

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

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

Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 525
Invalid length parameter passed to the LEFT or SUBSTRING function. Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 651
XML parsing: line 1, character 2, illegal xml character

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

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

--Recover the deleted data without date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'
GO
--Recover the deleted data it with date range
EXEC 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 log as per the search criteria

总结

实际上这个存储过程还是挺有研究意义的,对于想做一个跟Log Explorer for SQL Server软件功能差不多的软件出来

还是有可能的,跟着作者的思路,一步一步实现

苦于最近太忙,先分享出来,以后再研究这个存储过程了~