恢复SQLSERVER被误删除的数据

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

恢复SQLSERVER被误删除的数据

曾经想实现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 ,
 
 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