恢复SQLSERVER被误删除的数据
发布时间：2020-12-12 12:47:14 所属栏目：MsSql教程 来源：网络整理
导读：曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据 这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文： http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ ? 在我使用SQLSER
曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ ? 在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？” 现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据 （注意：这个脚本能恢复下面的数据类型的数据 而且兼容CS 排序规则）
让我来用demo来解释一下我是怎么做到的 USE master GO --创建数据库 CREATE DATABASE test GO USE [test] GO 创建表 TABLE dbo].aa]( id] intIDENTITY(1,1) NOT NULL,NAMEnvarchar](200) NULL ) ON PRIMARY插入测试数据 INSERT ] ( ] ) SELECT '你好' GO 删除数据 Delete from aa Go 验证数据是否已经删除 Select * Go 现在你需要创建一个存储过程来恢复你的数据
Script Name: Recover_Deleted_Data_Proc -- Script Type : Recovery Procedure Develop By: Muhammad Imran Date Created: 15 Oct 2011 Modify Date: 22 Aug 2012 Version : 3.1 Notes : Included BLOB data types for recovery.& Compatibile with Default,CS collation,Arabic_CI_AS. PROCEDURE Recover_Deleted_Data_Proc @Database_Name NVARCHAR(MAX),@SchemaName_n_TableName @Date_From DATETIME = 1900/01/01',0)">@Date_To 9999/12/31AS DECLARE @RowLogContents VARBINARY(8000) @TransactionID MAX) @AllocUnitID BIGINT @AllocUnitName @SQL @Compatibility_Level INT SELECT @Compatibility_Level = dtb.compatibility_level FROM master.sys.databases AS dtb WHERE dtb.name = @Database_Name IF ISNULL(@Compatibility_Level,0)">0) <= 80 BEGIN RAISERROR(The compatibility level should be equal to or greater SQL SERVER 2005 (90)',0)">16,0)">1) RETURN END IF ( COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA] + .' TABLE_NAME@SchemaName_n_TableName ) = 0 Could not found the table in the defined database@bitTable TABLE ( ID] INT,0)">BitvalueINT ) Create table to set the bit position of one byte. INSERT INTO @bitTable SELECT 0,2 UNION ALL 1,0)">2,0)">4 3,0)">8 4,0)">16 5,0)">32 6,0)">64 7,0)">128 Create table to collect the row data. @DeletedRecords Row IDINT 1),0)">RowLogContents8000),0)">AllocUnitIDBIGINT,0)">Transaction IDFixedLengthDataSMALLINT,0)">TotalNoOfColsNullBitMapLengthNullBytesTotalNoofVarColsColumnOffsetArrayVarColumnStartSlot IDNullBitMapVARCHAR(MAX) ) Create a common table expression to get all the row data plus how many bytes we have for each row. ; WITH RowData AS ( SELECT RowLog Contents 0AS ],0)">] [Fixed Length Data] = Substring (RowLog content 0,Status Bit A+ Status Bit B + 1,2 bytes) ,255)">CONVERT(SMALLINT,BINARY(2),255)">REVERSE(SUBSTRING(2 + 2)))) ] @FixedLengthData [TotalnoOfCols] = Substring (RowLog content 0,[Fixed Length Data] + 1,255)">INT,0)">2)))) ] [NullBitMapLength]=ceiling([Total No of Columns] /8.0) ,255)">CEILING(/ 8.0)) ] [Null Bytes] = Substring (RowLog content 0,Status Bit A+ Status Bit B + [Fixed Length Data] +1,[NullBitMapLength] ) ,0)">8.0))) [TotalNoofVarCols] = Substring (RowLog content 0,[Null Bitmap length] + 2 ) ,( CASE WHEN ],128)">IN ( 0x10,0)">0x30,0)">0x70 ) THEN 3 8.0)),0)">2)))) ELSE NULL END ) [ColumnOffsetArray]= Substring (RowLog content 0,[Null Bitmap length] + 2,[TotalNoofVarCols]*2 ) ,0)">3 8.0)) IN ( 0x10,0)">0x70 ) 2)))) NULL END ) * 2) Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2) ,128)">IN ( 0x70 ) THEN ( 4 8.0)) + ( ( 0x70 ) 2)))) NULL 2 ) ) NULL END ] FROM sys.fn_dblog(NULL,NULL) WHERE AllocUnitId IN ( SELECT Allocation_unit_id] FROM sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 3 ) AND partitions.hobt_id = allocunits.container_id ) OR ( allocunits.type 2 AND partitions.partition_id = allocunits.container_id ) WHERE object_id OBJECT_ID('' + @SchemaName_n_TableName '') ) AND Context IN ( LCX_MARK_AS_GHOSTLCX_HEAP' ) AND Operation LOP_DELETE_ROWSAND 0x70 ) /*Use this subquery to filter the date*/ TRANSACTION IDSELECT DISTINCT FROM sys.fn_dblog(NULL) WHERE Context LCX_NULL' ) LOP_BEGIN_XACTTransaction NameDELETEuser_transactionNVARCHAR(11),0)">Begin Time]) BETWEEN @Date_From AND @Date_To ) ),128)">Use this technique to repeate the row till the no of bytes of the row. N1 ( n ) SELECT 1 ALL 1 ),N2 ( n ) FROM N1 AS X,N1 AS Y ),N3 ( n ) FROM N2 SELECT ROW_NUMBER() OVER ( ORDER BY X.n ) FROM N3 AS Y ) @DeletedRecords SELECT RowLogContents,0)">] -Get the Null value against each column (1 means null zero means not null) ,128)">= ( REPLACE(STUFF(( SELECT ,0)">' + ( CASE 0 1),( SUBSTRING(NullBytes,n,128)">% 2 )) ELSE 1) / ] ) as [nullBitMap] FROM N4 AS Nums JOIN RowData AS C ON n <= NullBitMapLength CROSS JOIN @bitTable WHERE C.= D.] BY ASC FOR XML PATH('') ),0)">''),0)">'') ) FROM RowData D FROM @DeletedRecords ) There is no data in the log as per the search criteria@ColumnNameAndData RowlogcontentsVARBINARY(] SYSNAME,0)">nullbitleaf_offsetlengthsystem_type_idTINYINT,0)">bitposxprecxscaleis_nullColumn value SizeColumn Lengthhex_ValueUpdateINT ) Create common table expression and join it with the rowdata table to get each column details /*This part is for variable data columns*/ @RowLogContents,128)">(col.columnOffValue - col.columnLength) + 1,128)">col.columnLength) @ColumnNameAndData AS nullbit,leaf_offset,255)">ISNULL(syscolumns.length,cols.max_length) AS bitpos,255)">ISNULL(syscolumns.xprec,cols.precision) AS xprec,255)">ISNULL(syscolumns.xscale,cols.scale) AS xscale,0)">nullBitMap1) AS is_null,255)">WHEN leaf_offset < 1 0 * leaf_offset -1 ) - > 30000 2)))) - POWER(2,0)">15) 2)))) END ) 30000 ISNULL(NULLIF(* ( ( leaf_offset 1 ) ) 2)))),0)">0),0)">varColumnStartSystem_type_idIN ( 35,0)">34,0)">99 ) THEN 16 ELSE 24 END ) 24 2)))) ]) ) 15) ]) 1 THEN NULL SUBSTRING(Rowlogcontents,( ( 30000 2)))) 15) 2)))) END ) - ( 30000 30000 99 ) 16 24 24 2)))) ]) 15) ]) END ) ) 30000 30000 99 ) 16 24 24 ABS(])) 15) 2)))) ]) END )) AS hex_Value,0)">0 FROM @DeletedRecords A JOIN sys.allocation_units allocunits ON A.AllocUnitId= allocunits.Allocation_Unit_Id] = allocunits.container_id ) = allocunits.container_id ) 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 WHERE leaf_offset UNION This part is for fixed data columns*/ TOP 1 SUM(WHEN C.leaf_offset 1 THEN max_length 0 END),0)">0) FROM sys.system_internals_partition_columns C WHERE cols.partition_id = C.partition_id AND C.leaf_null_bit < cols.leaf_null_bit ) 5 1 NULL 1 1 AND C.leaf_bit_position THEN max_length 0) FROM sys.system_internals_partition_columns C = C.partition_id < cols.leaf_null_bit ) BY nullbit @BitColumnByte AS INT @BitColumnByte *) 8.0)) @ColumnNameAndData System_Type_id104; WITH N1 ( n ) SELECT RowLogContents,0)">BitMapSUBSTRING(( SUBSTRING(hex_Value,128)">as [nullBitMap] FROM N4 @ColumnNameAndData <= @BitColumnByte 104 AND bitpos @bitTable ] ASC FOR XML PATH('') ),0)">''),0)">'') ),bitpos 1))) FROM @ColumnNameAndData D WHERE 104 ) UPDATE A SET ] @ColumnNameAndData A JOIN CTE B = B.] AND A.] *************Check for BLOB DATA TYPES******************************/ @Fileid @Pageid @Slotid @CurrentLSN @LinkID @Context VARCHAR(50) @ConsolidatedPageID @LCX_TEXT_MIX MAX) @temppagedata ParentObjectObjectFieldValue] SYSNAME ) @pagedata Page IDFile IDSPage IDS@ModifiedRawData PAGE IDFILE IDSPAGE IDSRowLog Contents 0_varRowLog Length50),0)">RowLog LenLink IDINT DEFAULT ( 0 ),255)">INT ) DECLARE Page_Data_Cursor CURSOR FOR We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No,Page ID & AllocUnit IDLTRIM(RTRIM(DescriptionDeallocated''))) NULL NULL) WHERE AllocUnitId IN ( FROM sys.allocation_units allocunits '' @SchemaName_n_TableName '') ) LOP_MODIFY_ROW' ) ContextLCX_PFSAND Description LIKE %Deallocated%' */ DISTINCT NULL) ' @Date_To ) GROUP UNION 15,255)">LEN(])) 7,0)">2)),Context ,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN] LCX_TEXT_MIX' ) @Date_To ) ***************************************/ OPEN Page_Data_Cursor FETCH NEXT FROM Page_Data_Cursor @ConsolidatedPageID,0)">@Slotid,0)">@AllocUnitID,0)">@LCX_TEXT_MIX,0)">@LinkID,0)">@Context WHILE @@FETCH_STATUS @hex_pageid MAX) Page ID contains File Number and page number It looks like 0001:00000130. In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex formatSET @Fileid SUBSTRING(CHARINDEX(:@ConsolidatedPageID)) Seperate File ID from Page ID @hex_pageid 0x@ConsolidatedPageID,0)">@ConsolidatedPageID) LEN(@ConsolidatedPageID)) -Seperate the page ID @Pageid CAST('' AS XML).value(xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )varbinary(max)')) Convert Page ID from hex to integer FROM ( SELECT CASE @hex_pageid,0)">2) ' 3 0 END ) AS t ( pos ) IF @Context ' BEGIN DELETE @temppagedata @temppagedata EXEC ( DBCC PAGE(@DataBase_Name ' @fileid @pageid ' ); @pagedata @fileid,0)">@pageid,0)">@AllocUnitID,0)">@temppagedata END ELSE ' BEGIN @ModifiedRawData VARBINARY,0)">@LCX_TEXT_MIX,0)">11,0)">2)))),0)">@LinkID,0)">0 END @Context END CLOSE Page_Data_Cursor DEALLOCATE Page_Data_Cursor @Newhexstring MAX); The data is in multiple rows in the page,so we need to convert it into one row as a single hex value. This hex value is in string format @ModifiedRawData ( ] ) SlotOffset]) 4 ) ) 2) SUBSTRING(( ?]) ])),0)">'') FROM @pagedata C WHERE B.= C.] SUBSTRING(B.4 ) )) SUBSTRING(C.4 ) )) %Memory Dump%' LEFT(1) FOR XML PATH('') ),0)">' '') ),0)">20000) ' '') FROM @pagedata C WHERE B.' 1) FOR XML PATH('') ),0)">4) Length] @pagedata B ]] UPDATE @ModifiedRawData 8000),0)">xs:hexBinary(substring(sql:column("[RowLog Length]"),0))varbinary(Max)'))) LINK ID0 xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0)">') UPDATE B SET B.WHEN A.IS NULL AND C.NULL THEN A.] + C.] IS THEN C.] END ),255)">ISNULL(B.1 @ModifiedRawData B @ModifiedRawData A MAX),0)">15 14,0)">19 ] @ModifiedRawData C ON C.27 31 ] WHERE ( A.NULL OR C.NULL ) END ) <> B.] AND B.0 0 NULL ) >= 8000 15,0)">]) 6))))) ] @ColumnNameAndData A @ModifiedRawData B hex_value17,0)">4)))) ] 9,0)">2)) System_Type_Id99,0)">167,0)">175,0)">231,0)">239,0)">241,0)">165,0)">98 ) <> 0 WHEN B.NULL NULL THEN B.] ] @ColumnNameAndData A 5,128)">AND ( B.NULL NULL ) 3,0)">99 ) 0 0xFFFE ])) 241 #temp_Data] ( FieldNameFieldValueINTO #temp_Data SELECT NAME,255)">WHEN system_type_id 239 ) NVARCHAR,NCHAR 175 ) VARCHAR,CHAR 35 ) Text 99 ) nText 48 TINYINT,255)">REVERSE(hex_Value)))) TINY INTEGER 52 SMALL INTEGER 56 4),128)"> INTEGER 127 BIGINT,0)">8),255)">REVERSE(hex_Value)))) BIG INTEGER 61 DATETIME,255)">REVERSE(hex_Value))),0)">100) DATETIME 58 SMALLDATETIME,128)">SMALL DATETIME 108 CONVERT(NUMERIC(38,0)">20),xprec) + hex_Value)) - NUMERIC 106 DECIMAL(- DECIMAL 60,0)">122 ) MONEY,128)">MONEY,SMALLMONEY 104 CONVERT (BIT,hex_Value) 2)) BIT 62 STR(FLOAT,255)">SIGN(REVERSE(hex_Value)) BIGINT)) * ( 1.0 BIGINT) & 0x000FFFFFFFFFFFFF ) * POWER(CAST(2 FLOAT),0)">52) ) BIGINT) 0x7ff0000000000000 ) EXP(52 LOG(2)) 1023 ))),0)">53,255)">LEN(hex_Value)))) - FLOAT 59 BIGINT)) 1.0 BIGINT) 0x007FFFFF ) REAL),0)">23) ) REAL),( ( ( INT) ) 0x7f800000 ) 23 2)) 127 )) 23,0)">23)),0)">8) Real 173 ) CHARINDEX(0x,0)">xs:hexBinary(sql:column("hex_Value"))VARBINARY(8000)')) 0 ' '' varchar(max)') BINARY,VARBINARY 34 ') IMAGE 36 UNIQUEIDENTIFIER,hex_Value)) UNIQUEIDENTIFIER 231 CONVERT(SYSNAME,128)">SYSNAME 241 CONVERT(XML,128)">XML 189 TIMESTAMP 98 SUBSTRING(hex_Value,0)">1)) 56 LEN(hex_Value)))))) INTEGER 108 38,0)">1)) 4,0)">0) - NUMERIC 167 9,255)">LEN(hex_Value))))) 36 SUBSTRING(( hex_Value ),0)">20))) UNIQUEIDENTIFIER 61 LEN(hex_Value))))),128)">DATETIME 165 ' 0 ' '' END ) '),255)">LEN(hex_Value)) END ) AS FieldValue,255)">BY nullbit Create the column name in the same order to do pivot table. @FieldName @FieldName ' QUOTENAME(Name]) MAX)) FROM syscolumns WHERE id '' @SchemaName_n_TableName '') FOR XML PATH('') ),0)">'') Finally did pivot table and get the data back in the same format. @sql SELECT @FieldName FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' )) AS pvt' EXEC sp_executesql @sql GO 恢复你的数据 恢复数据，不加时间段条件 参数：数据库名，表名EXAMPLE #1 : FOR ALL DELETED RECORDS EXEC Recover_Deleted_Data_Proc 'dbo.恢复数据，加时间段条件EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE 2014-04-23'执行了下面的存储过程之后你会发现会显示出刚才删除的数据 dbo.aaGO
? 解释 究竟他是如何工作的?让我们来一步一步来,这个过程涉及到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可以说明是堆表还是聚集表 Operation：删除操作 AllocUnitName：分配单元名称，表名 下面是一个代码片段 NULL) WHERE AllocUnitName ' ) ' )这个查询会返回不同列的信息，但是我们只需要选择[RowLog Contents 0]列，去获得被删除的数据的内容 RowLog?content 0列的内容类似于这样 “0x300018000100000000000000006B0000564920205900000 00500E001002800426F62206A65727279″ 步骤2: 现在，我们已经删除了数据，这些数据以hex码的形式放在事务日志里，这些hex码是有规律的，我们根据这些规律可以很容易恢复这些数据。 不过在恢复这些数据之前，我们需要理解这些格式。这些格式在KalenDelaney’s SQL Internal’s book.的书里面有讲解 ?
所以,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码），利用上面的数据行的结构
?
步骤4: 现在我们已经将hex码切开了(0x300008000100000002000001001300604F7D59),所以,我们能找到删除的行的某列的数据是否为null值 根据NULL位图。为了完成将NULL Bytes的hex码转换为二进制格式（正如之前讨论的，1表示行中对应的那一列为null，而0则表示对应的列有实际的数据） 如果还不是明白的童鞋可以看一下我写的这篇文章：《SQLSERVER中NULL位图的作用》 ?
步骤5: 现在，我们已经做了初步的数据分割?(Step-3) 和null值判断(Step-4) 。然后我们需要使用代码片段去获得列数据，例如：列名，列大小，精度，范围 和最重要的叶子的null位（确保列数据是固定长度的（<=-1表示可变长度）或者固定长度的（>=1）） 使用下面的SQL语句 * FROM sys.allocation_units allocunits 3 ) = allocunits.container_id ) 2 = allocunits.container_id ) = partitions.partition_id object_id = cols.partition_column_id与(Step-1,2,3,4) 获得的数据表做join连接，根据allocunits.[Allocation_Unit_Id]。 现在我们知道表和表中的数据信息，那么我们需要利用这些数据去将?[RowLog?Contents 0] 列里的hex码的数据插入到表中的相应列 现在我们需要关心每一列的数据究竟是固定长度的还是可变长度的
步骤6: 我们收集了每列的hex格式的数据。现在我们需要利用[System_type_id]去转换这些数据回去正确的数据类型 每一种数据类型都有不同的数据类型转换机制。 IN (239) THEN max),hex_Value))) 175) max),255)">REPLACE(hex_Value,0)">0x00,0)">0x20)))) TINY INTEGER 48 REVERSE (hex_Value)))) SMALL INTEGER 52 INTEGER 56 REVERSE(hex_Value)))) BIG INTEGER 127 DATETIME 61 Then Max),255)">Convert(REVERSE (hex_Value))),0)">100) SMALL DATETIME =58 SMALL DATETIME - NUMERIC 108 18,0)">14),xprec)++ hex_Value) as FLOAT)) In(122) Reverse(hex_Value))),0)">2) - DECIMAL 106 Decimal(34),128)"> BIT 104 %2)) - FLOAT 62 Str(Reverse(hex_Value)) BIGINT)) * (1.0 + (BIGINT) 0x000FFFFFFFFFFFFF) FLOAT),0)">52)) 0x7ff0000000000000) 2))1023))),0)">53,255)">LEN(hex_Value)))) REAL When system_type_id 59 THEN Left(Cast(BIGINT))0x007FFFFF) Real),0)">23)) INT) )0x7f800000)127))23,0)">8) BINARY,VARBINARY In (173) THEN (Charindex(0x,255)">cast(xs:hexBinary(sql:column("hex_value"))0 END) ') UNIQUEIDENTIFIER 36
步骤7: 最终我们做一个数据透视表，你会看到最后的结果：被删的数据回来了！ 注意：这些数据只是展示出来并没有自动插入回表中，你需要将这些数据重新插入回去表中！
我的测试 经过测试，作者写的这个存储过程还是有些问题 如果你创建的测试表的数据类型有xml或者是一些text数据类型的字段会有报错 ? Msg 537,255)">Level Procedure Recover_Deleted_Data_Proc,Line 525 Invalid length parameter passed to the or SUBSTRING function. Msg 9420,0)">651 XML parsing: line character character 但是一般的数据类型则不会，例如nvarchar这些 还有不要在存储过程的最后加 Recover the deleted data without date range dbo.Test_TableRecover the deleted data it with date range 2012-06-012012-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软件功能差不多的软件出来 还是有可能的，跟着作者的思路，一步一步实现 苦于最近太忙，先分享出来，以后再研究这个存储过程了~ 如有不对的地方，欢迎大家拍砖o(∩_∩)o? 支持date类型 感谢园友?dwchaoyue
sssdbo.testdaterecover2014-10-23DROP TABLE testdaterecover TABLE testdaterecover(dd DATE,cc DATETIME) INSERT INTO testdaterecoverddccVALUES ( GETDATE(),128)"> dd - date GETDATE() cc - datetime ) 120) FROM ] DELETE ] 121) DATETIME （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
