时间：2021-07-01 10:21:17 帮助过：12人阅读
恢复SQLSERVER被误删除的数据 曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据 这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文： http://raresql.com/2011/10/22/how-to-recover-deleted-data-from
曾经想实现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 --创建表 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
现在你需要创建一个存储过程来恢复你的数据
-- 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. CREATE PROCEDURE Recover_Deleted_Data_Proc @Database_Name NVARCHAR(MAX) , @SchemaName_n_TableName NVARCHAR(MAX) , @Date_From DATETIME = '1900/01/01' , @Date_To DATETIME = '9999/12/31' AS DECLARE @RowLogContents VARBINARY(8000) DECLARE @TransactionID NVARCHAR(MAX) DECLARE @AllocUnitID BIGINT DECLARE @AllocUnitName NVARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) DECLARE @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) <= 80 BEGIN RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1) RETURN END IF ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName ) = 0 BEGIN RAISERROR('Could not found the table in the defined database',16,1) RETURN END DECLARE @bitTable TABLE ( [ID] INT , [Bitvalue] INT ) --Create table to set the bit position of one byte. INSERT INTO @bitTable SELECT 0 , 2 UNION ALL SELECT 1 , 2 UNION ALL SELECT 2 , 4 UNION ALL SELECT 3 , 8 UNION ALL SELECT 4 , 16 UNION ALL SELECT 5 , 32 UNION ALL SELECT 6 , 64 UNION ALL SELECT 7 , 128 --Create table to collect the row data. DECLARE @DeletedRecords TABLE ( [Row ID] INT IDENTITY(1, 1) , [RowLogContents] VARBINARY(8000) , [AllocUnitID] BIGINT , [Transaction ID] NVARCHAR(MAX) , [FixedLengthData] SMALLINT , [TotalNoOfCols] SMALLINT , [NullBitMapLength] SMALLINT , [NullBytes] VARBINARY(8000) , [TotalNoofVarCols] SMALLINT , [ColumnOffsetArray] VARBINARY(8000) , [VarColumnStart] SMALLINT , [Slot ID] INT , [NullBitMap] VARCHAR(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 0] AS [RowLogContents] , [AllocUnitID] AS [AllocUnitID] , [Transaction ID] AS [Transaction ID] --[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes) , CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] --@FixedLengthData -- [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes) , CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) AS [TotalNoOfCols] --[NullBitMapLength]=ceiling([Total No of Columns] /8.0) , CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)) AS [NullBitMapLength] --[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] ) , SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3, CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0))) AS [NullBytes] --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 ) , ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)), 2)))) ELSE NULL END ) AS [TotalNoofVarCols] --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 ) , ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THEN SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)) + 2, ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)), 2)))) ELSE NULL END ) * 2) ELSE NULL END ) AS [ColumnOffsetArray] -- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2) , CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THEN ( CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)) + ( ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)), 2)))) ELSE NULL END ) * 2 ) ) ELSE NULL END AS [VarColumnStart] , [Slot ID] 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 ( 1, 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_GHOST', 'LCX_HEAP' ) AND Operation IN ( 'LOP_DELETE_ROWS' ) AND SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) /*Use this subquery to filter the date*/ AND [TRANSACTION ID] IN ( SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) WHERE Context IN ( 'LCX_NULL' ) AND Operation IN ( 'LOP_BEGIN_XACT' ) AND [Transaction Name] IN ( 'DELETE', 'user_transaction' ) AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From AND @Date_To ) ), --Use this technique to repeate the row till the no of bytes of the row. N1 ( n ) AS ( SELECT 1 UNION ALL SELECT 1 ), N2 ( n ) AS ( SELECT 1 FROM N1 AS X , N1 AS Y ), N3 ( n ) AS ( SELECT 1 FROM N2 AS X , N2 AS Y ), N4 ( n ) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n ) FROM N3 AS X , N3 AS Y ) INSERT INTO @DeletedRecords SELECT RowLogContents , [AllocUnitID] , [Transaction ID] , [FixedLengthData] , [TotalNoOfCols] , [NullBitMapLength] , [NullBytes] , [TotalNoofVarCols] , [ColumnOffsetArray] , [VarColumnStart] , [Slot ID] ---Get the Null value against each column (1 means null zero means not null) , [NullBitMap] = ( REPLACE(STUFF(( SELECT ',' + ( CASE WHEN [ID] = 0 THEN CONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes, n, 1) % 2 )) ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes, n, 1) / [Bitvalue] ) % 2 )) END ) --as [nullBitMap] FROM N4 AS Nums JOIN RowData AS C ON n <= NullBitMapLength CROSS JOIN @bitTable WHERE C.[RowLogContents] = D.[RowLogContents] ORDER BY [RowLogContents] , n ASC FOR XML PATH('') ), 1, 1, ''), ',', '') ) FROM RowData D IF ( SELECT COUNT(*) FROM @DeletedRecords ) = 0 BEGIN RAISERROR('There is no data in the log as per the search criteria',16,1) RETURN END DECLARE @ColumnNameAndData TABLE ( [Row ID] INT , [Rowlogcontents] VARBINARY(MAX) , [NAME] SYSNAME , [nullbit] SMALLINT , [leaf_offset] SMALLINT , [length] SMALLINT , [system_type_id] TINYINT , [bitpos] TINYINT , [xprec] TINYINT , [xscale] TINYINT , [is_null] INT , [Column value Size] INT , [Column Length] INT , [hex_Value] VARBINARY(MAX) , [Slot ID] INT , [Update] INT ) --Create common table expression and join it with the rowdata table -- to get each column details /*This part is for variable data columns*/ --@RowLogContents, --(col.columnOffValue - col.columnLength) + 1, --col.columnLength --) INSERT INTO @ColumnNameAndData SELECT [Row ID] , Rowlogcontents , NAME , cols.leaf_null_bit AS nullbit , leaf_offset , ISNULL(syscolumns.length, cols.max_length) AS [length] , cols.system_type_id , cols.leaf_bit_position AS bitpos , ISNULL(syscolumns.xprec, cols.precision) AS xprec , ISNULL(syscolumns.xscale, cols.scale) AS xscale , SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null , ( CASE WHEN leaf_offset < 1 AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0 THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - POWER(2, 15) ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) END ) END ) AS [Column value Size] , ( CASE WHEN leaf_offset < 1 AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0 THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) < 30000 THEN ( CASE WHEN [System_type_id] IN ( 35, 34, 99 ) THEN 16 ELSE 24 END ) WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) > 30000 THEN ( CASE WHEN [System_type_id] IN ( 35, 34, 99 ) THEN 16 ELSE 24 END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) < 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) < 30000 THEN ( CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) ) WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) < 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) > 30000 THEN POWER(2, 15) + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) END ) END ) AS [Column Length] , ( CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1 THEN NULL ELSE SUBSTRING(Rowlogcontents, ( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - POWER(2, 15) ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) END ) - ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) < 30000 THEN ( CASE WHEN [System_type_id] IN ( 35, 34, 99 ) THEN 16 ELSE 24 END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) > 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) > 30000 THEN ( CASE WHEN [System_type_id] IN ( 35, 34, 99 ) THEN 16 ELSE 24 END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) < 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) < 30000 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) < 30000 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * ( ( leaf_offset * -1 ) - 1 ) ) - 1, 2)))), 0), [varColumnStart]) > 30000 THEN POWER(2, 15) + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2 * leaf_offset * -1 ) - 1, 2)))) - ISNULL