首页 > 代码库 > 转 --- 恢复误删数据
转 --- 恢复误删数据
曾经想实现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 masterGO--创建数据库CREATEDATABASE testGOUSE[test]GO--创建表CREATETABLE[dbo].[aa]( [id][int]IDENTITY(1,1) NOTNULL, [NAME][nvarchar](200) NULL) ON[PRIMARY]GO--插入测试数据INSERT[dbo].[aa] ( [NAME] )SELECT‘你好‘GO--删除数据Deletefrom aaGo--验证数据是否已经删除Select*from aaGo
现在你需要创建一个存储过程来恢复你的数据
-- 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.CREATEPROCEDURE Recover_Deleted_Data_Proc @Database_NameNVARCHAR(MAX) , @SchemaName_n_TableNameNVARCHAR(MAX) , @Date_FromDATETIME=‘1900/01/01‘ , @Date_ToDATETIME=‘9999/12/31‘ASDECLARE@RowLogContentsVARBINARY(8000) DECLARE@TransactionIDNVARCHAR(MAX) DECLARE@AllocUnitIDBIGINTDECLARE@AllocUnitNameNVARCHAR(MAX) DECLARE@SQLNVARCHAR(MAX) DECLARE@Compatibility_LevelINTSELECT@Compatibility_Level= dtb.compatibility_level FROM master.sys.databases AS dtb WHERE dtb.name =@Database_NameIFISNULL(@Compatibility_Level, 0) <=80BEGINRAISERROR(‘The compatibility level should be equal to or greater SQL SERVER 2005 (90)‘,16,1) RETURNENDIF ( SELECTCOUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE[TABLE_SCHEMA]+‘.‘+[TABLE_NAME]=@SchemaName_n_TableName ) =0BEGINRAISERROR(‘Could not found the table in the defined database‘,16,1) RETURNENDDECLARE@bitTableTABLE ( [ID]INT , [Bitvalue]INT )--Create table to set the bit position of one byte.INSERTINTO@bitTableSELECT0 , 2UNIONALLSELECT1 , 2UNIONALLSELECT2 , 4UNIONALLSELECT3 , 8UNIONALLSELECT4 , 16UNIONALLSELECT5 , 32UNIONALLSELECT6 , 64UNIONALLSELECT7 , 128--Create table to collect the row data.DECLARE@DeletedRecordsTABLE ( [Row ID]INTIDENTITY(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 ) , ( CASEWHENSUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THENCONVERT(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)))) ELSENULLEND ) AS[TotalNoofVarCols]--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 ) , ( CASEWHENSUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THENSUBSTRING([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, ( CASEWHENSUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THENCONVERT(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)))) ELSENULLEND ) *2) ELSENULLEND ) AS[ColumnOffsetArray]-- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2) , CASEWHENSUBSTRING([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)) + ( ( CASEWHENSUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) THENCONVERT(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)))) ELSENULLEND ) *2 ) ) ELSENULLENDAS[VarColumnStart] , [Slot ID]FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN ( SELECT[Allocation_unit_id]FROM sys.allocation_units allocunits INNERJOIN 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 ) WHEREobject_id=OBJECT_ID(‘‘+@SchemaName_n_TableName+‘‘) ) AND Context IN ( ‘LCX_MARK_AS_GHOST‘, ‘LCX_HEAP‘ ) AND Operation IN ( ‘LOP_DELETE_ROWS‘ ) ANDSUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 0x30, 0x70 ) /*Use this subquery to filter the date*/AND[TRANSACTION ID]IN ( SELECTDISTINCT[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‘ ) ANDCONVERT(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 ( SELECT1UNIONALLSELECT1 ), N2 ( n ) AS ( SELECT1FROM N1 AS X , N1 AS Y ), N3 ( n ) AS ( SELECT1FROM N2 AS X , N2 AS Y ), N4 ( n ) AS ( SELECT ROW_NUMBER() OVER ( ORDERBY X.n ) FROM N3 AS X , N3 AS Y ) INSERTINTO@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]=0THENCONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes, n, 1) %2 )) ELSECONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes, n, 1) /[Bitvalue] ) %2 )) END ) --as [nullBitMap]FROM N4 AS Nums JOIN RowData AS C ON n <= NullBitMapLength CROSSJOIN@bitTableWHERE C.[RowLogContents]= D.[RowLogContents]ORDERBY[RowLogContents] , n ASCFOR XML PATH(‘‘) ), 1, 1, ‘‘), ‘,‘, ‘‘) ) FROM RowData D IF ( SELECTCOUNT(*) FROM@DeletedRecords ) =0BEGINRAISERROR(‘There is no data in the log as per the search criteria‘,16,1) RETURNENDDECLARE@ColumnNameAndDataTABLE ( [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--)INSERTINTO@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 , ( CASEWHEN leaf_offset <1ANDSUBSTRING([nullBitMap], cols.leaf_null_bit, 1) =0THEN ( CASEWHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000THENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) -POWER(2, 15) ELSECONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) END ) END ) AS[Column value Size] , ( CASEWHEN leaf_offset <1ANDSUBSTRING([nullBitMap], cols.leaf_null_bit, 1) =0THEN ( CASEWHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000ANDISNULL(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 ) THEN16ELSE24END ) WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000ANDISNULL(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 ) THEN16ELSE24END ) --24 WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) <30000ANDISNULL(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]) ) WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) <30000ANDISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* ( ( leaf_offset *-1 ) -1 ) ) -1, 2)))), 0), [varColumnStart]) >30000THENPOWER(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] , ( CASEWHENSUBSTRING([nullBitMap], cols.leaf_null_bit, 1) =1THENNULLELSESUBSTRING(Rowlogcontents, ( ( CASEWHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000THENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) -POWER(2, 15) ELSECONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) END ) - ( CASEWHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000ANDISNULL(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 ) THEN16ELSE24END ) --24 WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000ANDISNULL(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 ) THEN16ELSE24END ) --24 WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) <30000ANDISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* ( ( leaf_offset *-1 ) -1 ) ) -1, 2)))), 0), [varColumnStart]) <30000THENCONVERT(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]) WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) <30000ANDISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* ( ( leaf_offset *-1 ) -1 ) ) -1, 2)))), 0), [varColumnStart]) >30000THENPOWER(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, ( CASEWHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000ANDISNULL(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 ) THEN16ELSE24END ) --24 WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) >30000ANDISNULL(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 ) THEN16ELSE24END ) --24 WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) <30000ANDISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* ( ( leaf_offset *-1 ) -1 ) ) -1, 2)))), 0), [varColumnStart]) <30000THENABS(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])) WHENCONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* leaf_offset *-1 ) -1, 2)))) <30000ANDISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], ( 2* ( ( leaf_offset *-1 ) -1 ) ) -1, 2)))), 0), [varColumnStart]) >30000THENPOWER(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 A INNERJOIN sys.allocation_units allocunits ON A.[AllocUnitId]= allocunits.[Allocation_Unit_Id]INNERJOIN 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 ) INNERJOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id LEFTOUTERJOIN syscolumns ON syscolumns.id = partitions.object_idAND syscolumns.colid = cols.partition_column_id WHERE 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 , ( SELECTTOP1ISNULL(SUM(CASEWHEN C.leaf_offset >1THEN max_length ELSE0END), 0) FROM sys.system_internals_partition_columns C WHERE cols.partition_id = C.partition_id AND C.leaf_null_bit < cols.leaf_null_bit ) +5AS[Column value Size] , syscolumns.length AS[Column Length] , CASEWHENSUBSTRING([nullBitMap], cols.leaf_null_bit, 1) =1THENNULLELSESUBSTRING(Rowlogcontents, ( SELECTTOP1ISNULL(SUM(CASEWHEN C.leaf_offset >1AND C.leaf_bit_position =0THEN max_length ELSE0END), 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, syscolumns.length) ENDAS hex_Value , [Slot ID] , 0FROM@DeletedRecords A INNERJOIN sys.allocation_units allocunits ON A.[AllocUnitId]= allocunits.[Allocation_Unit_Id]INNERJOIN 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 ) INNERJOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id LEFTOUTERJOIN syscolumns ON syscolumns.id = partitions.object_idAND syscolumns.colid = cols.partition_column_id WHERE leaf_offset >0ORDERBY nullbit DECLARE@BitColumnByteASINTSELECT@BitColumnByte=CONVERT(INT, CEILING(COUNT(*) /8.0)) FROM@ColumnNameAndDataWHERE[System_Type_id]=104; WITH N1 ( n ) AS ( SELECT1UNIONALLSELECT1 ), N2 ( n ) AS ( SELECT1FROM N1 AS X , N1 AS Y ), N3 ( n ) AS ( SELECT1FROM N2 AS X , N2 AS Y ), N4 ( n ) AS ( SELECT ROW_NUMBER() OVER ( ORDERBY X.n ) FROM N3 AS X , N3 AS Y ), CTE AS ( SELECT RowLogContents , [nullbit] , [BitMap]=CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT‘,‘+ ( CASEWHEN[ID]=0THENCONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value, n, 1) %2 )) ELSECONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value, n, 1) /[Bitvalue] ) %2 )) END ) --as [nullBitMap]FROM N4 AS Nums JOIN@ColumnNameAndDataAS C ON n <=@BitColumnByteAND[System_Type_id]=104AND bitpos =0CROSSJOIN@bitTableWHERE C.[RowLogContents]= D.[RowLogContents]ORDERBY[RowLogContents] , n ASCFOR XML PATH(‘‘) ), 1, 1, ‘‘), ‘,‘, ‘‘) ), bitpos +1, 1))) FROM@ColumnNameAndData D WHERE[System_Type_id]=104 ) UPDATE A SET[hex_Value]=[BitMap]FROM@ColumnNameAndData A INNERJOIN CTE B ON A.[RowLogContents]= B.[RowLogContents]AND A.[nullbit]= B.[nullbit]/**************Check for BLOB DATA TYPES******************************/DECLARE@FileidINTDECLARE@PageidINTDECLARE@SlotidINTDECLARE@CurrentLSNINTDECLARE@LinkIDINTDECLARE@ContextVARCHAR(50) DECLARE@ConsolidatedPageIDVARCHAR(MAX) DECLARE@LCX_TEXT_MIXVARBINARY(MAX) DECLARE@temppagedataTABLE ( [ParentObject] SYSNAME , [Object] SYSNAME , [Field] SYSNAME , [Value] SYSNAME ) DECLARE@pagedataTABLE ( [Page ID] SYSNAME , [File IDS]INT , [Page IDS]INT , [AllocUnitId]BIGINT , [ParentObject] SYSNAME , [Object] SYSNAME , [Field] SYSNAME , [Value] SYSNAME ) DECLARE@ModifiedRawDataTABLE ( [ID]INTIDENTITY(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]INTDEFAULT ( 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*/SELECTLTRIM(RTRIM(REPLACE([Description], ‘Deallocated‘, ‘‘))) AS[PAGE ID] , [Slot ID] , [AllocUnitId] , NULLAS[RowLog Contents 0] , NULLAS[RowLog Contents 0] , Context FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN ( SELECT[Allocation_unit_id]FROM sys.allocation_units allocunits INNERJOIN 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 ) WHEREobject_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 ( SELECTDISTINCT[TRANSACTION ID]FROM sys.fn_dblog(NULL, NULL) WHERE Context IN ( ‘LCX_NULL‘ ) AND Operation IN ( ‘LOP_BEGIN_XACT‘ ) AND[Transaction Name]=‘DELETE‘ANDCONVERT(NVARCHAR(11), [Begin Time]) BETWEEN@Date_FromAND@Date_To ) GROUPBY[Description] , [Slot ID] , [AllocUnitId] , Context UNIONSELECT[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 sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN ( SELECT[Allocation_unit_id]FROM sys.allocation_units allocunits INNERJOIN 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 ) WHEREobject_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 ( SELECTDISTINCT[TRANSACTION ID]FROM sys.fn_dblog(NULL, NULL) WHERE Context IN ( ‘LCX_NULL‘ ) AND Operation IN ( ‘LOP_BEGIN_XACT‘ ) AND[Transaction Name]=‘DELETE‘ANDCONVERT(NVARCHAR(11), [Begin Time]) BETWEEN@Date_FromAND@Date_To ) /****************************************/OPEN Page_Data_Cursor FETCHNEXTFROM Page_Data_Cursor INTO@ConsolidatedPageID, @Slotid, @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @ContextWHILE@@FETCH_STATUS=0BEGINDECLARE@hex_pageidASVARCHAR(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 ( SELECTCASESUBSTRING(@hex_pageid, 1, 2) WHEN‘0x‘THEN3ELSE0END ) AS t ( pos ) IF@Context=‘LCX_PFS‘BEGINDELETE@temppagedataINSERTINTO@temppagedataEXEC ( ‘DBCC PAGE(‘+@DataBase_Name+‘, ‘+@fileid+‘, ‘+@pageid+‘, 1) with tableresults,no_infomsgs;‘ ); INSERTINTO@pagedataSELECT@ConsolidatedPageID , @fileid , @pageid , @AllocUnitID , [ParentObject] , [Object] , [Field] , [Value]FROM@temppagedataENDELSEIF@Context=‘LCX_TEXT_MIX‘BEGININSERTINTO@ModifiedRawDataSELECT@ConsolidatedPageID , @fileid , @pageid , @Slotid , @AllocUnitID , NULL , 0 , CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX, 11, 2)))) , @LCX_TEXT_MIX , @LinkID , 0ENDFETCHNEXTFROM Page_Data_Cursor INTO@ConsolidatedPageID, @Slotid, @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @ContextENDCLOSE Page_Data_Cursor DEALLOCATE Page_Data_Cursor DECLARE@NewhexstringVARCHAR(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 formatINSERTINTO@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(( SELECTREPLACE(STUFF(( SELECTREPLACE(SUBSTRING([Value], CHARINDEX(‘:‘, [Value]) +1, CHARINDEX(‘†‘, [Value]) -CHARINDEX(‘:‘, [Value])), ‘†‘, ‘‘) FROM@pagedata C WHERE B.[Page ID]= C.[Page ID]ANDSUBSTRING(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%‘ORDERBY‘0x‘+LEFT([Value], CHARINDEX(‘:‘, [Value]) -1) FOR XML PATH(‘‘) ), 1, 1, ‘‘), ‘‘, ‘‘) ), 1, 20000) AS[Value] , SUBSTRING(( SELECT‘0x‘+REPLACE(STUFF(( SELECTREPLACE(SUBSTRING([Value], CHARINDEX(‘:‘, [Value]) +1, CHARINDEX(‘†‘, [Value]) -CHARINDEX(‘:‘, [Value])), ‘†‘, ‘‘) FROM@pagedata C WHERE B.[Page ID]= C.[Page ID]ANDSUBSTRING(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%‘ORDERBY‘0x‘+LEFT([Value], CHARINDEX(‘:‘, [Value]) -1) FOR XML PATH(‘‘) ), 1, 1, ‘‘), ‘‘, ‘‘) ), 7, 4) AS[Length]FROM@pagedata B WHERE[Object]LIKE‘%Memory Dump%‘GROUPBY[Page ID] , [FILE IDS] , [PAGE IDS] , [ParentObject] , [AllocUnitId]--,[Current LSN]ORDERBY[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 B SET B.[RowLog Contents 0]= ( CASEWHEN A.[RowLog Contents 0]ISNOTNULLAND C.[RowLog Contents 0]ISNOTNULLTHEN A.[RowLog Contents 0]+ C.[RowLog Contents 0]WHEN A.[RowLog Contents 0]ISNULLAND C.[RowLog Contents 0]ISNOTNULLTHEN C.[RowLog Contents 0]WHEN A.[RowLog Contents 0]ISNOTNULLAND C.[RowLog Contents 0]ISNULLTHEN A.[RowLog Contents 0]END ) , B.[Update]=ISNULL(B.[Update], 0) +1FROM@ModifiedRawData B LEFTJOIN@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]LEFTJOIN@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]ISNOTNULLOR C.[RowLog Contents 0]ISNOTNULL ) UPDATE B SET B.[RowLog Contents 0]= ( CASEWHEN A.[RowLog Contents 0]ISNOTNULLAND C.[RowLog Contents 0]ISNOTNULLTHEN A.[RowLog Contents 0]+ C.[RowLog Contents 0]WHEN A.[RowLog Contents 0]ISNULLAND C.[RowLog Contents 0]ISNOTNULLTHEN C.[RowLog Contents 0]WHEN A.[RowLog Contents 0]ISNOTNULLAND C.[RowLog Contents 0]ISNULLTHEN A.[RowLog Contents 0]END ) --,B.[Update]=ISNULL(B.[Update],0)+1FROM@ModifiedRawData B LEFTJOIN@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]=0LEFTJOIN@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]ISNOTNULLOR C.[RowLog Contents 0]ISNOTNULL ) UPDATE@ModifiedRawDataSET[RowLog Contents 0]= ( CASEWHEN[RowLog Len]>=8000THENSUBSTRING([RowLog Contents 0], 15, [RowLog Len]) WHEN[RowLog Len]<8000THENSUBSTRING([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 A INNERJOIN@ModifiedRawData B ONCONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value], 17, 4)))) =[PAGE IDS]ANDCONVERT(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]<>0UPDATE@ColumnNameAndDataSET[hex_Value]= ( CASEWHEN B.[RowLog Contents 0]ISNOTNULLAND C.[RowLog Contents 0]ISNOTNULLTHEN B.[RowLog Contents 0]+ C.[RowLog Contents 0]WHEN B.[RowLog Contents 0]ISNULLAND C.[RowLog Contents 0]ISNOTNULLTHEN C.[RowLog Contents 0]WHEN B.[RowLog Contents 0]ISNOTNULLAND C.[RowLog Contents 0]ISNULLTHEN B.[RowLog Contents 0]END ) --,A.[Update]=A.[Update]+1FROM@ColumnNameAndData A LEFTJOIN@ModifiedRawData B ONCONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value], 5, 4)))) = B.[PAGE IDS]AND B.[Link ID]=0LEFTJOIN@ModifiedRawData C ONCONVERT(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]ISNOTNULLOR C.[RowLog Contents 0]ISNOTNULL ) UPDATE@ColumnNameAndDataSET[hex_Value]=[RowLog Contents 0]--,A.[Update]=A.[Update]+1FROM@ColumnNameAndData A INNERJOIN@ModifiedRawData B ONCONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value], 9, 4)))) =[PAGE IDS]ANDCONVERT(INT, SUBSTRING([hex_value], 3, 2)) =[Link ID]WHERE[System_Type_Id]IN ( 35, 34, 99 ) AND[Link ID]<>0UPDATE@ColumnNameAndDataSET[hex_Value]=[RowLog Contents 0]--,A.[Update]=A.[Update]+10FROM@ColumnNameAndData A INNERJOIN@ModifiedRawData B ONCONVERT(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 A INNERJOIN@ModifiedRawData B ONCONVERT(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]=241CREATETABLE[#temp_Data] ( [FieldName]VARCHAR(MAX) , [FieldValue]NVARCHAR(MAX) , [Rowlogcontents]VARBINARY(8000) , [Row ID]INT ) INSERTINTO #temp_Data SELECT NAME , CASEWHEN system_type_id IN ( 231, 239 ) THENLTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --NVARCHAR ,NCHARWHEN system_type_id IN ( 167, 175 ) THENLTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --VARCHAR,CHARWHEN system_type_id IN ( 35 ) THENLTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --TextWHEN system_type_id IN ( 99 ) THENLTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --nText WHEN system_type_id =48THENCONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE(hex_Value)))) --TINY INTEGERWHEN system_type_id =52THENCONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGERWHEN system_type_id =56THENCONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGERWHEN system_type_id =127THENCONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGERWHEN system_type_id =61THENCONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --DATETIMEWHEN system_type_id =58THENCONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIMEWHEN system_type_id =108THENCONVERT(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 =106THENCONVERT(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 ) THENCONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEYWHEN system_type_id =104THENCONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value) %2)) -- BITWHEN system_type_id =62THENRTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) ASBIGINT)) * ( 1.0+ ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) ASBIGINT) &0x000FFFFFFFFFFFFF ) *POWER(CAST(2ASFLOAT), -52) ) *POWER(CAST(2ASFLOAT), ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) ASBIGINT) &0x7ff0000000000000 ) /EXP(52*LOG(2)) -1023 ))), 53, LEN(hex_Value)))) --- FLOATWHEN system_type_id =59THENLEFT(LTRIM(STR(CAST(SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) ASBIGINT)) * ( 1.0+ ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) ASBIGINT) &0x007FFFFF ) *POWER(CAST(2ASREAL), -23) ) *POWER(CAST(2ASREAL), ( ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) ASINT) ) &0x7f800000 ) /EXP(23*LOG(2)) -127 )) ASREAL), 23, 23)), 8) --RealWHEN system_type_id IN ( 165, 173 ) THEN ( CASEWHENCHARINDEX(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 ( CASEWHENCHARINDEX(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 =36THENCONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value)) --UNIQUEIDENTIFIERWHEN system_type_id =231THENCONVERT(VARCHAR(MAX), CONVERT(SYSNAME, hex_Value)) --SYSNAMEWHEN system_type_id =241THENCONVERT(VARCHAR(MAX), CONVERT(XML, hex_Value)) --XMLWHEN system_type_id =189THEN ( CASEWHENCHARINDEX(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 ( CASEWHENCONVERT(INT, SUBSTRING(hex_Value, 1, 1)) =56THENCONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value, 3, LEN(hex_Value)))))) -- INTEGERWHENCONVERT(INT, SUBSTRING(hex_Value, 1, 1)) =108THENCONVERT(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)))) --- NUMERICWHENCONVERT(INT, SUBSTRING(hex_Value, 1, 1)) =167THENLTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value, 9, LEN(hex_Value))))) --VARCHAR,CHARWHENCONVERT(INT, SUBSTRING(hex_Value, 1, 1)) =36THENCONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ), 3, 20))) --UNIQUEIDENTIFIERWHENCONVERT(INT, SUBSTRING(hex_Value, 1, 1)) =61THENCONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value, 3, LEN(hex_Value))))), 100) --DATETIMEWHENCONVERT(INT, SUBSTRING(hex_Value, 1, 1)) =165THEN‘0x‘+SUBSTRING(( CASEWHENCHARINDEX(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 ) ENDAS FieldValue , [Rowlogcontents] , [Row ID]FROM@ColumnNameAndDataORDERBY nullbit --Create the column name in the same order to do pivot table.DECLARE@FieldNameVARCHAR(MAX) SET@FieldName=STUFF(( SELECT‘,‘+CAST(QUOTENAME([Name]) ASVARCHAR(MAX)) FROM syscolumns WHERE id =OBJECT_ID(‘‘+@SchemaName_n_TableName+‘‘) FOR XML 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
恢复你的数据
--恢复数据，不加时间段条件 参数：数据库名，表名--EXAMPLE #1 : FOR ALL DELETED RECORDSEXEC Recover_Deleted_Data_Proc ‘test‘,‘dbo.aa‘GO--恢复数据，加时间段条件--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGEEXEC Recover_Deleted_Data_Proc ‘test‘,‘dbo.aa‘,‘2014-04-23‘,‘2014-04-23‘
执行了下面的存储过程之后你会发现会显示出刚才删除的数据
EXEC Recover_Deleted_Data_Proc ‘test‘,‘dbo.aa‘GO
解释
究竟他是如何工作的?让我们来一步一步来,这个过程涉及到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存储结构之堆表、行溢出
关于数据行的结构我们还可以采用稍微宏观一些的视角来查看。
步骤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)
步骤4:
现在我们已经将hex码切开了(0x300008000100000002000001001300604F7D59),所以,我们能找到删除的行的某列的数据是否为null值
根据NULL位图。为了完成将NULL Bytes的hex码转换为二进制格式（正如之前讨论的，1表示行中对应的那一列为null，而0则表示对应的列有实际的数据）
如果还不是明白的童鞋可以看一下我写的这篇文章：《SQLSERVER中NULL位图的作用》
步骤5:
现在，我们已经做了初步的数据分割 (Step-3) 和null值判断(Step-4) 。然后我们需要使用代码片段去获得列数据，例如：列名，列大小，精度，范围
和最重要的叶子的null位（确保列数据是固定长度的（<=-1表示可变长度）或者固定长度的（>=1））
使用下面的SQL语句
SELECT*FROM sys.allocation_units allocunits INNERJOIN 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 ) INNERJOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id LEFTOUTERJOIN syscolumns ON syscolumns.id = partitions.object_idAND syscolumns.colid = cols.partition_column_id
与(Step-1,2,3,4) 获得的数据表做join连接，根据allocunits.[Allocation_Unit_Id]。
现在我们知道表和表中的数据信息，那么我们需要利用这些数据去将 [RowLog Contents 0] 列里的hex码的数据插入到表中的相应列
现在我们需要关心每一列的数据究竟是固定长度的还是可变长度的
步骤6:
我们收集了每列的hex格式的数据。现在我们需要利用[System_type_id]去转换这些数据回去正确的数据类型
每一种数据类型都有不同的数据类型转换机制。
--NVARCHAR ,NCHARWHEN system_type_id IN (231, 239) THENLTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --VARCHAR,CHARWHEN system_type_id IN (167,175) THENLTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20)))) --TINY INTEGERWHEN system_type_id =48THENCONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --SMALL INTEGERWHEN system_type_id =52THENCONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) -- INTEGERWHEN system_type_id =56THENCONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- BIG INTEGERWHEN system_type_id =127THENCONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value)))) --DATETIMEWHEN system_type_id =61ThenCONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100) --SMALL DATETIMEWHEN system_type_id =58ThenCONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME--- NUMERICWHEN system_type_id =108THENCONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) asFLOAT)) --MONEY,SMALLMONEYWHEN system_type_id In(60,122) THENCONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2) --- DECIMALWHEN system_type_id =106THENCONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), Convert(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) asFLOAT)) -- BITWHEN system_type_id =104THENCONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) --- FLOATWHEN system_type_id =62THENRTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) ASBIGINT)) * (1.0+ (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASBIGINT) &0x000FFFFFFFFFFFFF) *POWER(CAST(2ASFLOAT), -52)) *POWER(CAST(2ASFLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASBIGINT) &0x7ff0000000000000) /EXP(52*LOG(2))-1023))),53,LEN(hex_Value)))) --REALWhen system_type_id =59THENLeft(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) ASBIGINT))* (1.0+ (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASBIGINT) &0x007FFFFF) *POWER(CAST(2ASReal), -23)) *POWER(CAST(2ASReal),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) ASINT) )&0x7f800000)/EXP(23*LOG(2))-127))ASREAL),23,23)),8) --BINARY,VARBINARYWHEN system_type_id In (165,173) THEN (CASEWHENCharindex(0x,cast(‘‘AS XML).value(‘xs:hexBinary(sql:column("hex_value"))‘, ‘varbinary(max)‘)) =0THEN‘0x‘ELSE‘‘END) +cast(‘‘AS XML).value(‘xs:hexBinary(sql:column("hex_value"))‘, ‘varchar(max)‘) --UNIQUEIDENTIFIER WHEN system_type_id =36THENCONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) 
步骤7:
最终我们做一个数据透视表，你会看到最后的结果：被删的数据回来了！
注意：这些数据只是展示出来并没有自动插入回表中，你需要将这些数据重新插入回去表中！
我的测试
经过测试，作者写的这个存储过程还是有些问题
如果你创建的测试表的数据类型有xml或者是一些text数据类型的字段会有报错
Msg 537, Level16, State 3, Procedure Recover_Deleted_Data_Proc, Line 525Invalid length parameter passed to the LEFTorSUBSTRINGfunction.Msg 9420, Level16, State 1, Procedure Recover_Deleted_Data_Proc, Line 651XML parsing: line 1, character2, illegal xml character
但是一般的数据类型则不会，例如nvarchar这些
还有不要在存储过程的最后加
--Recover the deleted data without date rangeEXEC Recover_Deleted_Data_Proc ‘test‘,‘dbo.Test_Table‘GO--Recover the deleted data it with date rangeEXEC 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 logas per the search criteria
总结
实际上这个存储过程还是挺有研究意义的，对于想做一个跟Log Explorer for SQL Server软件功能差不多的软件出来
还是有可能的，跟着作者的思路，一步一步实现
苦于最近太忙，先分享出来，以后再研究这个存储过程了~
转 --- 恢复误删数据
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。