原文:恢复SQL Server被误删除的数据（再扩展）

恢复SQL Server被误删除的数据（再扩展）

 

大家对本人之前的文章《恢复SQL Server被误删除的数据》 反应非常热烈，但是文章里的存储过程不能实现对备份出来的日志备份里所删数据的恢复

这个是一个缺陷，本人决定对这个存储过程扩展一下，支持对log backup文件里的delete语句进行恢复

 

实验步骤

 

1、首先先准备好测试表和测试语句

USE [sss] GO--建表 CREATE TABLE testdelete(id INT IDENTITY(1, 1)NOT NULLPRIMARY KEY ,NAME VARCHAR(200) ,dt DATETIME)--插入数据 INSERT [dbo].[testdelete]( [NAME], [dt] ) VALUES ( 'aa', -- NAME - varchar(200)'2015-07-04 07:06:40' -- dt - datetime )SELECT * FROM [dbo].[testdelete]--删除数据 DELETE FROM [dbo].[testdelete]

 

2、删除数据之后对数据库进行日志备份

DECLARE @CurrentTime VARCHAR(50) ,@FileName VARCHAR(200) SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120),'-', '_'), ' ', '_'), ':', '') SET @FileName = 'c:\sss_logBackup_' + @CurrentTime + '.bak' BACKUP LOG [sss] TO DISK=@FileName WITH FORMAT

 

4、建立存储过程

-- Script Name: Recover_Deleted_Data_BylogBackup_Proc -- Script Type : Recovery Procedure -- Develop By: Steven Lam -- Date Created: 03 July 2015 -- Version : 1.0 -- Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS.USE [sss] GOCREATE PROCEDURE Recover_Deleted_Data_BylogBackup_Proc@Database_Name NVARCHAR(MAX) ,@SchemaName_n_TableName NVARCHAR(MAX) ,@Backuppath NVARCHAR(2000),@Date_From DATETIME = '1900/01/01' ,@Date_To DATETIME = '9999/12/31' ASDECLARE @RowLogContents VARBINARY(8000)DECLARE @TransactionID NVARCHAR(MAX)DECLARE @AllocUnitID BIGINTDECLARE @AllocUnitName NVARCHAR(MAX)DECLARE @SQL NVARCHAR(MAX)DECLARE @Compatibility_Level INTIF ( @Backuppath IS NULLOR @Backuppath = '')BEGINRAISERROR('The parameter @Backuppath can not be null!',16,1)RETURNENDSELECT @Compatibility_Level = dtb.compatibility_levelFROM master.sys.databases AS dtbWHERE dtb.name = @Database_NameIF ISNULL(@Compatibility_Level, 0) <= 80BEGINRAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)RETURNENDIF ( SELECT COUNT(*)FROM INFORMATION_SCHEMA.TABLESWHERE [TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName) = 0BEGINRAISERROR('Could not found the table in the defined database',16,1)RETURNENDDECLARE @bitTable TABLE([ID] INT ,[Bitvalue] INT) --Create table to set the bit position of one byte.INSERT INTO @bitTableSELECT 0 ,2UNION ALLSELECT 1 ,2UNION ALLSELECT 2 ,4UNION ALLSELECT 3 ,8UNION ALLSELECT 4 ,16UNION ALLSELECT 5 ,32UNION ALLSELECT 6 ,64UNION ALLSELECT 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 RowDataAS ( 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 NULLEND ) 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 NULLEND ) * 2)ELSE NULLEND ) 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 NULLEND ) * 2 ) )ELSE NULLEND AS [VarColumnStart] ,[Slot ID]FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT)WHERE AllocUnitId IN (SELECT [Allocation_unit_id]FROM sys.allocation_units allocunitsINNER JOIN 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)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 fn_dump_dblog(NULL, NULL, N'DISK', 1,@Backuppath, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT)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_FromAND@Date_To )),--Use this technique to repeate the row till the no of bytes of the row. N1 ( n )AS ( SELECT 1UNION ALLSELECT 1),N2 ( n )AS ( SELECT 1FROM N1 AS X ,N1 AS Y),N3 ( n )AS ( SELECT 1FROM 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 @DeletedRecordsSELECT 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','+ ( CASEWHEN [ID] = 0THEN CONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes,n, 1) % 2 ))ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes,n, 1)/ [Bitvalue] )% 2 ))END ) --as [nullBitMap]FROM N4 AS NumsJOIN RowData AS C ON n <= NullBitMapLengthCROSS JOIN @bitTableWHEREC.[RowLogContents] = D.[RowLogContents]ORDER BY [RowLogContents] ,n ASCFORXML PATH('')), 1, 1, ''), ',', '') )FROM RowData DIF ( SELECT COUNT(*)FROM @DeletedRecords) = 0BEGINRAISERROR('There is no data in the log as per the search criteria',16,1)RETURNENDDECLARE @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 @ColumnNameAndDataSELECT [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 < 1AND SUBSTRING([nullBitMap], cols.leaf_null_bit,1) = 0THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000THEN 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 < 1AND SUBSTRING([nullBitMap], cols.leaf_null_bit,1) = 0THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) < 30000THEN ( CASE WHEN [System_type_id] IN (35, 34, 99 ) THEN 16ELSE 24END )WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) > 30000THEN ( CASE WHEN [System_type_id] IN (35, 34, 99 ) THEN 16ELSE 24END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) < 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) < 30000THEN ( 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)))) < 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) > 30000THEN 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) = 1THEN NULLELSE SUBSTRING(Rowlogcontents,( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000THEN 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)))) > 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) < 30000THEN ( CASEWHEN [System_type_id] IN (35, 34, 99 )THEN 16ELSE 24END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) > 30000THEN ( CASEWHEN [System_type_id] IN (35, 34, 99 )THEN 16ELSE 24END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) < 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) < 30000THEN 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)))) < 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) > 30000THEN 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 ) ) + 1,( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) < 30000THEN ( CASE WHEN [System_type_id] IN (35, 34, 99 )THEN 16ELSE 24END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) > 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) > 30000THEN ( CASE WHEN [System_type_id] IN (35, 34, 99 )THEN 16ELSE 24END ) --24 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* leaf_offset* -1 ) - 1, 2)))) < 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) < 30000THEN ABS(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)))) < 30000AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],( 2* ( ( leaf_offset* -1 ) - 1 ) )- 1, 2)))), 0),[varColumnStart]) > 30000THEN 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 hex_Value ,[Slot ID] ,0FROM @DeletedRecords AINNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]INNER JOIN 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)INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_idLEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_idAND syscolumns.colid = cols.partition_column_idWHERE leaf_offset < 0UNION /*This part is for fixed data columns*/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 ,( SELECT TOP 1ISNULL(SUM(CASE WHEN C.leaf_offset > 1THEN max_lengthELSE 0END), 0)FROM sys.system_internals_partition_columns CWHERE cols.partition_id = C.partition_idAND C.leaf_null_bit < cols.leaf_null_bit) + 5 AS [Column value Size] ,syscolumns.length AS [Column Length] ,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1THEN NULLELSE SUBSTRING(Rowlogcontents,( SELECT TOP 1ISNULL(SUM(CASEWHEN C.leaf_offset > 1AND C.leaf_bit_position = 0THEN max_lengthELSE 0END), 0)FROM sys.system_internals_partition_columns CWHERE cols.partition_id = C.partition_idAND C.leaf_null_bit < cols.leaf_null_bit) + 5, syscolumns.length)END AS hex_Value ,[Slot ID] ,0FROM @DeletedRecords AINNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]INNER JOIN 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)INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_idLEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_idAND syscolumns.colid = cols.partition_column_idWHERE leaf_offset > 0ORDER BY nullbitDECLARE @BitColumnByte AS INTSELECT @BitColumnByte = CONVERT(INT, CEILING(COUNT(*) / 8.0))FROM @ColumnNameAndDataWHERE [System_Type_id] = 104;WITH N1 ( n )AS ( SELECT 1UNION ALLSELECT 1),N2 ( n )AS ( SELECT 1FROM N1 AS X ,N1 AS Y),N3 ( n )AS ( SELECT 1FROM N2 AS X ,N2 AS Y),N4 ( n )AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n )FROM N3 AS X ,N3 AS Y),CTEAS ( SELECT RowLogContents ,[nullbit] ,[BitMap] = CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT','+ ( CASEWHEN [ID] = 0THEN CONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value,n, 1) % 2 ))ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value,n, 1)/ [Bitvalue] )% 2 ))END ) --as [nullBitMap]FROMN4 AS NumsJOIN @ColumnNameAndDataAS C ON n <= @BitColumnByteAND [System_Type_id] = 104AND bitpos = 0CROSS JOIN @bitTableWHEREC.[RowLogContents] = D.[RowLogContents]ORDER BY [RowLogContents] ,n ASCFORXMLPATH('')), 1, 1, ''),',', '') ),bitpos + 1, 1)))FROM @ColumnNameAndData DWHERE [System_Type_id] = 104)UPDATE ASET [hex_Value] = [BitMap]FROM @ColumnNameAndData AINNER JOIN CTE B ON A.[RowLogContents] = B.[RowLogContents]AND A.[nullbit] = B.[nullbit]/**************Check for BLOB DATA TYPES******************************/DECLARE @Fileid INTDECLARE @Pageid INTDECLARE @Slotid INTDECLARE @CurrentLSN INTDECLARE @LinkID INTDECLARE @Context VARCHAR(50)DECLARE @ConsolidatedPageID VARCHAR(MAX)DECLARE @LCX_TEXT_MIX VARBINARY(MAX)DECLARE @temppagedata TABLE([ParentObject] SYSNAME ,[Object] SYSNAME ,[Field] SYSNAME ,[Value] SYSNAME)DECLARE @pagedata TABLE([Page ID] SYSNAME ,[File IDS] INT ,[Page IDS] INT ,[AllocUnitId] BIGINT ,[ParentObject] SYSNAME ,[Object] SYSNAME ,[Field] SYSNAME ,[Value] SYSNAME)DECLARE @ModifiedRawData TABLE([ID] INT IDENTITY(1, 1) ,[PAGE ID] VARCHAR(MAX) ,[FILE IDS] INT ,[PAGE IDS] INT ,[Slot ID] INT ,[AllocUnitId] BIGINT ,[RowLog Contents 0_var] VARCHAR(MAX) ,[RowLog Length] VARCHAR(50) ,[RowLog Len] INT ,[RowLog Contents 0] VARBINARY(MAX) ,[Link ID] INT DEFAULT ( 0 ) ,[Update] INT)DECLARE Page_Data_Cursor CURSORFOR/*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 ID*/SELECT LTRIM(RTRIM(REPLACE([Description], 'Deallocated', ''))) AS [PAGE ID] ,[Slot ID] ,[AllocUnitId] ,NULL AS [RowLog Contents 0] ,NULL AS [RowLog Contents 0] ,ContextFROM fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT)WHERE AllocUnitId IN (SELECT [Allocation_unit_id]FROM sys.allocation_units allocunitsINNER JOIN 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)WHERE object_id = OBJECT_ID('' + @SchemaName_n_TableName+ '') )AND Operation IN ( 'LOP_MODIFY_ROW' )AND [Context] IN ( 'LCX_PFS' )AND Description LIKE '%Deallocated%'/*Use this subquery to filter the date*/AND [TRANSACTION ID] IN (SELECT DISTINCT[TRANSACTION ID]FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT)WHERE Context IN ( 'LCX_NULL' )AND Operation IN ( 'LOP_BEGIN_XACT' )AND [Transaction Name] = 'DELETE'AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_FromAND@Date_To )GROUP BY [Description] ,[Slot ID] ,[AllocUnitId] ,ContextUNIONSELECT [PAGE ID] ,[Slot ID] ,[AllocUnitId] ,SUBSTRING([RowLog Contents 0], 15,LEN([RowLog Contents 0])) AS [RowLog Contents 0] ,CONVERT(INT, SUBSTRING([RowLog Contents 0], 7, 2)) ,Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT)WHERE AllocUnitId IN (SELECT [Allocation_unit_id]FROM sys.allocation_units allocunitsINNER JOIN 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)WHERE object_id = OBJECT_ID('' + @SchemaName_n_TableName+ '') )AND Context IN ( 'LCX_TEXT_MIX' )AND Operation IN ( 'LOP_DELETE_ROWS' ) /*Use this subquery to filter the date*/AND [TRANSACTION ID] IN (SELECT DISTINCT[TRANSACTION ID]FROM fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT)WHERE Context IN ( 'LCX_NULL' )AND Operation IN ( 'LOP_BEGIN_XACT' )AND [Transaction Name] = 'DELETE'AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_FromAND@Date_To )/****************************************/OPEN Page_Data_CursorFETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID, @LCX_TEXT_MIX, @LinkID, @ContextWHILE @@FETCH_STATUS = 0BEGINDECLARE @hex_pageid AS VARCHAR(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 format*/SET @Fileid = SUBSTRING(@ConsolidatedPageID, 0,CHARINDEX(':', @ConsolidatedPageID)) -- Seperate File ID from Page IDSET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+ 1, LEN(@ConsolidatedPageID)) ---Seperate the page IDSELECT @Pageid = CONVERT(INT, CAST('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )','varbinary(max)')) -- Convert Page ID from hex to integerFROM ( SELECT CASE SUBSTRING(@hex_pageid, 1, 2)WHEN '0x' THEN 3ELSE 0END) AS t ( pos ) IF @Context = 'LCX_PFS'BEGINDELETE @temppagedataINSERT INTO @temppagedataEXEC( 'DBCC PAGE(' + @DataBase_Name + ', '+ @fileid + ', ' + @pageid+ ', 1) with tableresults,no_infomsgs;'); INSERT INTO @pagedataSELECT @ConsolidatedPageID ,@fileid ,@pageid ,@AllocUnitID ,[ParentObject] ,[Object] ,[Field] ,[Value]FROM @temppagedataENDELSEIF @Context = 'LCX_TEXT_MIX'BEGININSERT INTO @ModifiedRawDataSELECT @ConsolidatedPageID ,@fileid ,@pageid ,@Slotid ,@AllocUnitID ,NULL ,0 ,CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX,11, 2)))) ,@LCX_TEXT_MIX ,@LinkID ,0END FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID, @LCX_TEXT_MIX, @LinkID, @ContextENDCLOSE Page_Data_CursorDEALLOCATE Page_Data_CursorDECLARE @Newhexstring VARCHAR(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 formatINSERT INTO @ModifiedRawData( [PAGE ID] ,[FILE IDS] ,[PAGE IDS] ,[Slot ID] ,[AllocUnitId] ,[RowLog Contents 0_var] ,[RowLog Length])SELECT [Page ID] ,[FILE IDS] ,[PAGE IDS] ,SUBSTRING([ParentObject],CHARINDEX('Slot', [ParentObject]) + 4,( CHARINDEX('Offset', [ParentObject])- ( CHARINDEX('Slot', [ParentObject]) + 4 ) )- 2) AS [Slot ID] ,[AllocUnitId] ,SUBSTRING(( SELECT REPLACE(STUFF(( SELECTREPLACE(SUBSTRING([Value],CHARINDEX(':',[Value]) + 1,CHARINDEX('†',[Value])- CHARINDEX(':',[Value])), '†','')FROM @pagedata CWHERE B.[Page ID] = C.[Page ID]AND SUBSTRING(B.[ParentObject],CHARINDEX('Slot',B.[ParentObject])+ 4,( CHARINDEX('Offset',B.[ParentObject])- ( CHARINDEX('Slot',B.[ParentObject])+ 4 ) )) = SUBSTRING(C.[ParentObject],CHARINDEX('Slot',C.[ParentObject])+ 4,( CHARINDEX('Offset',C.[ParentObject])- ( CHARINDEX('Slot',C.[ParentObject])+ 4 ) ))AND [Object] LIKE '%Memory Dump%'ORDER BY '0x'+ LEFT([Value],CHARINDEX(':',[Value]) - 1)FORXML PATH('')), 1, 1, ''), ' ', '')), 1, 20000) AS [Value] ,SUBSTRING(( SELECT '0x'+ REPLACE(STUFF(( SELECTREPLACE(SUBSTRING([Value],CHARINDEX(':',[Value]) + 1,CHARINDEX('†',[Value])- CHARINDEX(':',[Value])), '†','')FROM@pagedata CWHEREB.[Page ID] = C.[Page ID]AND SUBSTRING(B.[ParentObject],CHARINDEX('Slot',B.[ParentObject])+ 4,( CHARINDEX('Offset',B.[ParentObject])- ( CHARINDEX('Slot',B.[ParentObject])+ 4 ) )) = SUBSTRING(C.[ParentObject],CHARINDEX('Slot',C.[ParentObject])+ 4,( CHARINDEX('Offset',C.[ParentObject])- ( CHARINDEX('Slot',C.[ParentObject])+ 4 ) ))AND [Object] LIKE '%Memory Dump%'ORDER BY '0x'+ LEFT([Value],CHARINDEX(':',[Value]) - 1)FORXML PATH('')), 1, 1, ''), ' ', '')), 7, 4) AS [Length]FROM @pagedata BWHERE [Object] LIKE '%Memory Dump%'GROUP BY [Page ID] ,[FILE IDS] ,[PAGE IDS] ,[ParentObject] ,[AllocUnitId]--,[Current LSN]ORDER BY [Slot ID]UPDATE @ModifiedRawDataSET [RowLog Len] = CONVERT(VARBINARY(8000), REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))','varbinary(Max)')))FROM @ModifiedRawDataWHERE [LINK ID] = 0UPDATE @ModifiedRawDataSET [RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))','varbinary(Max)')FROM @ModifiedRawDataWHERE [LINK ID] = 0UPDATE BSET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULLAND C.[RowLog Contents 0] IS NOT NULLTHEN A.[RowLog Contents 0]+ C.[RowLog Contents 0]WHEN A.[RowLog Contents 0] IS NULLAND C.[RowLog Contents 0] IS NOT NULLTHEN C.[RowLog Contents 0]WHEN A.[RowLog Contents 0] IS NOT NULLAND C.[RowLog Contents 0] IS NULLTHEN A.[RowLog Contents 0]END ) ,B.[Update] = ISNULL(B.[Update], 0) + 1FROM @ModifiedRawData BLEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],15 + 14, 2))))AND A.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],19 + 14, 2))))AND A.[Link ID] = B.[Link ID]LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],27 + 14, 2))))AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],31 + 14, 2))))AND C.[Link ID] = B.[Link ID]WHERE ( A.[RowLog Contents 0] IS NOT NULLOR C.[RowLog Contents 0] IS NOT NULL)UPDATE BSET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULLAND C.[RowLog Contents 0] IS NOT NULLTHEN A.[RowLog Contents 0]+ C.[RowLog Contents 0]WHEN A.[RowLog Contents 0] IS NULLAND C.[RowLog Contents 0] IS NOT NULLTHEN C.[RowLog Contents 0]WHEN A.[RowLog Contents 0] IS NOT NULLAND C.[RowLog Contents 0] IS NULLTHEN A.[RowLog Contents 0]END )--,B.[Update]=ISNULL(B.[Update],0)+1FROM @ModifiedRawData BLEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],15 + 14, 2))))AND A.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],19 + 14, 2))))AND A.[Link ID] <> B.[Link ID]AND B.[Update] = 0LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],27 + 14, 2))))AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],31 + 14, 2))))AND C.[Link ID] <> B.[Link ID]AND B.[Update] = 0WHERE ( A.[RowLog Contents 0] IS NOT NULLOR C.[RowLog Contents 0] IS NOT NULL)UPDATE @ModifiedRawDataSET [RowLog Contents 0] = ( CASE WHEN [RowLog Len] >= 8000THEN SUBSTRING([RowLog Contents 0],15, [RowLog Len])WHEN [RowLog Len] < 8000THEN SUBSTRING([RowLog Contents 0],15 + 6,CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents 0],15, 6)))))END )FROM @ModifiedRawDataWHERE [LINK ID] = 0UPDATE @ColumnNameAndDataSET [hex_Value] = [RowLog Contents 0] --,A.[Update]=A.[Update]+1FROM @ColumnNameAndData AINNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],17, 4)))) = [PAGE IDS]AND CONVERT(INT, SUBSTRING([hex_value],9, 2)) = B.[Link ID]WHERE [System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )AND [Link ID] <> 0 UPDATE @ColumnNameAndDataSET [hex_Value] = ( CASE WHEN B.[RowLog Contents 0] IS NOT NULLAND C.[RowLog Contents 0] IS NOT NULLTHEN B.[RowLog Contents 0]+ C.[RowLog Contents 0]WHEN B.[RowLog Contents 0] IS NULLAND C.[RowLog Contents 0] IS NOT NULLTHEN C.[RowLog Contents 0]WHEN B.[RowLog Contents 0] IS NOT NULLAND C.[RowLog Contents 0] IS NULLTHEN B.[RowLog Contents 0]END )--,A.[Update]=A.[Update]+1FROM @ColumnNameAndData ALEFT JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],5, 4)))) = B.[PAGE IDS]AND B.[Link ID] = 0LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],17, 4)))) = C.[PAGE IDS]AND C.[Link ID] = 0WHERE [System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )AND ( B.[RowLog Contents 0] IS NOT NULLOR C.[RowLog Contents 0] IS NOT NULL)UPDATE @ColumnNameAndDataSET [hex_Value] = [RowLog Contents 0] --,A.[Update]=A.[Update]+1FROM @ColumnNameAndData AINNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],9, 4)))) = [PAGE IDS]AND CONVERT(INT, SUBSTRING([hex_value],3, 2)) = [Link ID]WHERE [System_Type_Id] IN ( 35, 34, 99 )AND [Link ID] <> 0 UPDATE @ColumnNameAndDataSET [hex_Value] = [RowLog Contents 0]--,A.[Update]=A.[Update]+10FROM @ColumnNameAndData AINNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],9, 4)))) = [PAGE IDS]WHERE [System_Type_Id] IN ( 35, 34, 99 )AND [Link ID] = 0UPDATE @ColumnNameAndDataSET [hex_Value] = [RowLog Contents 0] --,A.[Update]=A.[Update]+1FROM @ColumnNameAndData AINNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],15, 4)))) = [PAGE IDS]WHERE [System_Type_Id] IN ( 35, 34, 99 )AND [Link ID] = 0UPDATE @ColumnNameAndDataSET [hex_value] = 0xFFFE + SUBSTRING([hex_value], 9, LEN([hex_value]))--,[Update]=[Update]+1WHERE [system_type_id] = 241CREATE TABLE [#temp_Data]([FieldName] VARCHAR(MAX) ,[FieldValue] NVARCHAR(MAX) ,[Rowlogcontents] VARBINARY(8000) ,[Row ID] INT)INSERT INTO #temp_DataSELECT NAME ,CASE WHEN system_type_id IN ( 231, 239 )THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --NVARCHAR ,NCHARWHEN system_type_id IN ( 167, 175 )THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --VARCHAR,CHARWHEN system_type_id IN ( 35 )THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --TextWHEN system_type_id IN ( 99 )THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --nText WHEN system_type_id = 48THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE(hex_Value)))) --TINY INTEGERWHEN system_type_id = 52THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGERWHEN system_type_id = 56THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGERWHEN system_type_id = 127THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGERWHEN system_type_id = 61THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --DATETIMEWHEN system_type_id = 58THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIMEWHEN system_type_id = 108THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)+ CONVERT(VARBINARY(1), xscale))+ CONVERT(VARBINARY(1), 0) + hex_Value)) --- NUMERICWHEN system_type_id = 106THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)+ CONVERT(VARBINARY(1), xscale))+ CONVERT(VARBINARY(1), 0) + hex_Value)) --- DECIMALWHEN system_type_id IN ( 60, 122 )THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEYWHEN system_type_id = 104THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value)% 2)) -- BITWHEN system_type_id = 62THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))* ( 1.0+ ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)& 0x000FFFFFFFFFFFFF )* POWER(CAST(2 AS FLOAT),-52) )* POWER(CAST(2 AS FLOAT),( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)& 0x7ff0000000000000 )/ EXP(52 * LOG(2))- 1023 ))), 53,LEN(hex_Value)))) --- FLOATWHEN system_type_id = 59THEN LEFT(LTRIM(STR(CAST(SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))* ( 1.0+ ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)& 0x007FFFFF )* POWER(CAST(2 AS REAL), -23) )* POWER(CAST(2 AS REAL),( ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS INT) )& 0x7f800000 )/ EXP(23 * LOG(2))- 127 )) AS REAL), 23,23)), 8) --RealWHEN system_type_id IN ( 165, 173 )THEN ( CASE WHEN CHARINDEX(0x,CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','VARBINARY(8000)')) = 0THEN '0x'ELSE ''END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)') -- BINARY,VARBINARYWHEN system_type_id = 34THEN ( CASE WHEN CHARINDEX(0x,CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','VARBINARY(8000)')) = 0THEN '0x'ELSE ''END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)') --IMAGEWHEN system_type_id = 36THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value)) --UNIQUEIDENTIFIERWHEN system_type_id = 231THEN CONVERT(VARCHAR(MAX), CONVERT(SYSNAME, hex_Value)) --SYSNAMEWHEN system_type_id = 241THEN CONVERT(VARCHAR(MAX), CONVERT(XML, hex_Value)) --XMLWHEN system_type_id = 189THEN ( CASE WHEN CHARINDEX(0x,CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','VARBINARY(8000)')) = 0THEN '0x'ELSE ''END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)') --TIMESTAMPWHEN system_type_id = 98THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1)) = 56THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value,3,LEN(hex_Value)))))) -- INTEGERWHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1)) = 108THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38,20), CONVERT(VARBINARY(1), SUBSTRING(hex_Value,3, 1))+ CONVERT(VARBINARY(1), SUBSTRING(hex_Value,4, 1))+ CONVERT(VARBINARY(1), 0)+ SUBSTRING(hex_Value, 5,LEN(hex_Value)))) --- NUMERICWHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1)) = 167THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,9,LEN(hex_Value))))) --VARCHAR,CHARWHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1)) = 36THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),3, 20))) --UNIQUEIDENTIFIERWHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1)) = 61THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value,3,LEN(hex_Value))))), 100) --DATETIMEWHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1)) = 165THEN '0x'+ SUBSTRING(( CASE WHEN CHARINDEX(0x,CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','VARBINARY(8000)')) = 0THEN '0x'ELSE ''END )+ CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)'),11, LEN(hex_Value)) -- BINARY,VARBINARYEND )END AS FieldValue ,[Rowlogcontents] ,[Row ID]FROM @ColumnNameAndDataORDER BY nullbit--Create the column name in the same order to do pivot table.DECLARE @FieldName VARCHAR(MAX)SET @FieldName = STUFF(( SELECT ','+ CAST(QUOTENAME([Name]) AS VARCHAR(MAX))FROM syscolumnsWHERE id = OBJECT_ID(''+ @SchemaName_n_TableName+ '')FORXML PATH('')), 1, 1, '')--Finally did pivot table and get the data back in the same format.SET @sql = 'SELECT ' + @FieldName+ ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN ('+ @FieldName + ')) AS pvt'EXEC sp_executesql @sqlGO View Code

 

5、在C盘看到备份出来的日志备份文件

 

 

6、存储过程的两种调用方式

--存储过程依然保持着两种调用方式 EXEC Recover_Deleted_Data_BylogBackup_Proc 'sss','dbo.testdelete',N'C:\sss_logBackup_2015_07_04_150756.BAK' GOEXEC Recover_Deleted_Data_BylogBackup_Proc 'sss','dbo.testdelete',N'C:\sss_logBackup_2015_07_04_150756.BAK','2012-06-01','2016-06-30' GO

 

可以看到被误删的那条数据.

 

---

为什麽要扩展这个存储过程

 

因为进行了日志备份之后，原先的delete语句的log record可能已经不存在于ldf文件里面，这时候要恢复被那条delete语句删除的数据就需要读取

log backup文件里面的log record来进行恢复

 

对比图

Recover_Deleted_Data_Proc

Recover_Deleted_Data_BylogBackup_Proc

 

因为很多公司为了数据安全，一般会对数据库半个小时或者15分钟做一次日志备份，目的是做日志传送或者及时把数据库备份拷走

考虑到这种情况，本人决定扩展一下这个存储过程，其实原理也很简单，就是把fn_dblog函数替换为fn_dump_dblog函数

 

在文章《恢复SQL Server被误删除的数据》发布之后，有很多人通过QQ咨询我，为什麽执行了你的存储过程之后会显示

There is no data in the log as per the search criteria

 

我发现原因基本上有两个：

（1）数据库从来没有做过完整备份

（2）因为数据库实例下有一个job定时对数据库做日志备份，误删除了数据之后（误执行delete语句之后），刚好那个job到达运行时间，对数据库进行了日志备份

 

对于第一种情况，可以参考本人写的这篇文章《您真的理解了SQLSERVER的日志链了吗？》，文章里面有解释

对于第二种情况，可以使用本篇文章的方法，回想一下delete语句的执行时间，然后指定日志备份的备份文件进行恢复

--指定日志备份的备份文件 EXEC Recover_Deleted_Data_BylogBackup_Proc 'sss','dbo.testdelete',N'C:\sss_logBackup_2015_07_04_150756.BAK' GO

 

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

总结

以上是生活随笔为你收集整理的恢复SQL Server被误删除的数据（再扩展）的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。