----创建存储过程
CREATE PROCEDURE Recover_Deleted_Data_Proc
@Database_Name NVARCHAR(MAX) ,
@SchemaName_n_TableName NVARCHAR(MAX) ,
@Date_From DATETIME
=
'
1900/01/01
'
,
@Date_To DATETIME
=
'
9999/12/31
'
AS
DECLARE @RowLogContents VARBINARY(
8000
)
DECLARE @TransactionID NVARCHAR(MAX)
DECLARE @AllocUnitID BIGINT
DECLARE @AllocUnitName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Compatibility_Level INT
SELECT @Compatibility_Level
=
dtb.compatibility_level
FROM master.sys.databases AS dtb
WHERE dtb.name
=
@Database_Name
IF ISNULL(@Compatibility_Level,
0
) <=
80
BEGIN
RAISERROR(
'
The compatibility level should be equal to or greater SQL SERVER 2005 (90)
'
,
16
,
1
)
RETURN
END
IF ( SELECT COUNT(
*
)
FROM INFORMATION_SCHEMA.TABLES
WHERE [TABLE_SCHEMA]
+
'
.
'
+ [TABLE_NAME] =
@SchemaName_n_TableName
)
=
0
BEGIN
RAISERROR(
'
Could not found the table in the defined database
'
,
16
,
1
)
RETURN
END
DECLARE @bitTable TABLE
(
[ID] INT ,
[Bitvalue] INT
)
--Create table to
set
the bit position of one
byte
.
INSERT INTO @bitTable
SELECT
0
,
2
UNION ALL
SELECT
1
,
2
UNION ALL
SELECT
2
,
4
UNION ALL
SELECT
3
,
8
UNION ALL
SELECT
4
,
16
UNION ALL
SELECT
5
,
32
UNION ALL
SELECT
6
,
64
UNION ALL
SELECT
7
,
128
--
Create table to collect the row data.
DECLARE @DeletedRecords TABLE
(
[Row ID] INT IDENTITY(
1
,
1
) ,
[RowLogContents] VARBINARY(
8000
) ,
[AllocUnitID] BIGINT ,
[Transaction ID] NVARCHAR(MAX) ,
[FixedLengthData] SMALLINT ,
[TotalNoOfCols] SMALLINT ,
[NullBitMapLength] SMALLINT ,
[NullBytes] VARBINARY(
8000
) ,
[TotalNoofVarCols] SMALLINT ,
[ColumnOffsetArray] VARBINARY(
8000
) ,
[VarColumnStart] SMALLINT ,
[Slot ID] INT ,
[NullBitMap] VARCHAR(MAX)
)
--Create a common table expression to
get
all the row data plus how many bytes we have
for
each row.
;
WITH RowData
AS ( SELECT [RowLog Contents
0
] AS [RowLogContents] ,
[AllocUnitID] AS [AllocUnitID] ,
[Transaction ID] AS [Transaction ID]
--[Fixed Length Data] = Substring (RowLog content
0
, Status Bit A+ Status Bit B +
1
,
2
bytes)
,
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) AS [FixedLengthData] --
@FixedLengthData
-- [TotalnoOfCols] = Substring (RowLog content
0
, [Fixed Length Data] +
1
,
2
bytes)
,
CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) AS [TotalNoOfCols]
--[NullBitMapLength]=ceiling([Total No of Columns] /
8.0
)
,
CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
)) AS [NullBitMapLength]
--[Null Bytes] = Substring (RowLog content
0
, Status Bit A+ Status Bit B + [Fixed Length Data] +
1
, [NullBitMapLength] )
,
SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
3
,
CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
))) AS [NullBytes]
--[TotalNoofVarCols] = Substring (RowLog content
0
, Status Bit A+ Status Bit B + [Fixed Length Data] +
1
, [Null Bitmap length] +
2
)
,
( CASE WHEN SUBSTRING([RowLog Contents
0
],
1
,
1
) IN (
0x10
,
0x30
,
0x70
)
THEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
3
+ CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
)),
2
))))
ELSE NULL
END ) AS [TotalNoofVarCols]
--[ColumnOffsetArray]= Substring (RowLog content
0
, Status Bit A+ Status Bit B + [Fixed Length Data] +
1
, [Null Bitmap length] +
2
, [TotalNoofVarCols]*
2
)
,
( CASE WHEN SUBSTRING([RowLog Contents
0
],
1
,
1
) IN (
0x10
,
0x30
,
0x70
)
THEN SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
3
+ CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
))
+
2
,
( CASE WHEN SUBSTRING([RowLog Contents
0
],
1
,
1
) IN (
0x10
,
0x30
,
0x70
)
THEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
3
+ CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
)),
2
))))
ELSE NULL
END )
*
2
)
ELSE NULL
END ) AS [ColumnOffsetArray]
-- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] +
2
+([TotalNoofVarCols]*
2
)
,
CASE WHEN SUBSTRING([RowLog Contents
0
],
1
,
1
) IN (
0x10
,
0x30
,
0x70
)
THEN ( CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
4
+ CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
))
+ ( ( CASE WHEN SUBSTRING([RowLog Contents
0
],
1
,
1
) IN (
0x10
,
0x30
,
0x70
)
THEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
3
+ CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([RowLog Contents
0
],
2
+
1
,
2
)))) +
1
,
2
)))) /
8.0
)),
2
))))
ELSE NULL
END )
*
2
) )
ELSE NULL
END AS [VarColumnStart] ,
[Slot ID]
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitId IN (
SELECT [Allocation_unit_id]
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
1
,
3
)
AND partitions.hobt_id
=
allocunits.container_id
)
OR ( allocunits.type
=
2
AND partitions.partition_id
=
allocunits.container_id
)
WHERE object_id
= OBJECT_ID(
''
+
@SchemaName_n_TableName
+
''
) )
AND Context IN (
'
LCX_MARK_AS_GHOST
'
,
'
LCX_HEAP
'
)
AND Operation IN (
'
LOP_DELETE_ROWS
'
)
AND SUBSTRING([RowLog Contents
0
],
1
,
1
) IN (
0x10
,
0x30
,
0x70
)
/*
Use this subquery to filter the date
*/
AND [TRANSACTION ID] IN (
SELECT DISTINCT
[TRANSACTION ID]
FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN (
'
LCX_NULL
'
)
AND Operation IN (
'
LOP_BEGIN_XACT
'
)
AND [Transaction Name] IN (
'
DELETE
'
,
'
user_transaction
'
)
AND CONVERT(NVARCHAR(
11
), [Begin Time]) BETWEEN @Date_From
AND
@Date_To )
),
--Use
this
technique to repeate the row till the no of bytes of the row.
N1 ( n )
AS ( SELECT
1
UNION ALL
SELECT
1
),
N2 ( n )
AS ( SELECT
1
FROM N1 AS X ,
N1 AS Y
),
N3 ( n )
AS ( SELECT
1
FROM N2 AS X ,
N2 AS Y
),
N4 ( n )
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n )
FROM N3 AS X ,
N3 AS Y
)
INSERT INTO @DeletedRecords
SELECT RowLogContents ,
[AllocUnitID] ,
[Transaction ID] ,
[FixedLengthData] ,
[TotalNoOfCols] ,
[NullBitMapLength] ,
[NullBytes] ,
[TotalNoofVarCols] ,
[ColumnOffsetArray] ,
[VarColumnStart] ,
[Slot ID]
---Get the Null value against each column (
1
means
null
zero means not
null
)
,
[NullBitMap]
=
( REPLACE(STUFF(( SELECT
'
,
'
+
( CASE
WHEN [ID]
=
0
THEN CONVERT(NVARCHAR(
1
), ( SUBSTRING(NullBytes,
n,
1
) %
2
))
ELSE CONVERT(NVARCHAR(
1
), ( ( SUBSTRING(NullBytes,
n,
1
)
/
[Bitvalue] )
%
2
))
END )
--
as
[nullBitMap]
FROM N4 AS Nums
JOIN RowData AS C ON n
<=
NullBitMapLength
CROSS JOIN @bitTable
WHERE
C.[RowLogContents]
=
D.[RowLogContents]
ORDER BY [RowLogContents] ,
n ASC
FOR
XML PATH(
''
)
),
1
,
1
,
''
),
'
,
'
,
''
) )
FROM RowData D
IF ( SELECT COUNT(
*
)
FROM @DeletedRecords
)
=
0
BEGIN
RAISERROR(
'
There is no data in the log as per the search criteria
'
,
16
,
1
)
RETURN
END
DECLARE @ColumnNameAndData TABLE
(
[Row ID] INT ,
[Rowlogcontents] VARBINARY(MAX) ,
[NAME] SYSNAME ,
[nullbit] SMALLINT ,
[leaf_offset] SMALLINT ,
[length] SMALLINT ,
[system_type_id] TINYINT ,
[bitpos] TINYINT ,
[xprec] TINYINT ,
[xscale] TINYINT ,
[is_null] INT ,
[Column value Size] INT ,
[Column Length] INT ,
[hex_Value] VARBINARY(MAX) ,
[Slot ID] INT ,
[Update] INT
)
--
Create common table expression and join it with the rowdata table
-- to
get
each column details
/*
This part is for variable data columns
*/
--
@RowLogContents,
--(col.columnOffValue - col.columnLength) +
1
,
--
col.columnLength
--
)
INSERT INTO @ColumnNameAndData
SELECT [Row ID] ,
Rowlogcontents ,
NAME ,
cols.leaf_null_bit AS nullbit ,
leaf_offset ,
ISNULL(syscolumns.length, cols.max_length) AS [length] ,
cols.system_type_id ,
cols.leaf_bit_position AS bitpos ,
ISNULL(syscolumns.xprec, cols.precision) AS xprec ,
ISNULL(syscolumns.xscale, cols.scale) AS xscale ,
SUBSTRING([nullBitMap], cols.leaf_null_bit,
1
) AS is_null ,
( CASE WHEN leaf_offset
<
1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit,
1
) =
0
THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) >
30000
THEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
- POWER(
2
,
15
)
ELSE CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
END )
END ) AS [Column value Size] ,
( CASE WHEN leaf_offset
<
1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit,
1
) =
0
THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) >
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
<
30000
THEN ( CASE WHEN [System_type_id] IN (
35
,
34
,
99
) THEN
16
ELSE
24
END )
WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) >
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
>
30000
THEN ( CASE WHEN [System_type_id] IN (
35
,
34
,
99
) THEN
16
ELSE
24
END )
--
24
WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) <
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
<
30000
THEN ( CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart]) )
WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) <
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
>
30000
THEN POWER(
2
,
15
)
+ CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
END )
END ) AS [Column Length] ,
( CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit,
1
) =
1
THEN NULL
ELSE SUBSTRING(Rowlogcontents,
( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) >
30000
THEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
- POWER(
2
,
15
)
ELSE CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
END )
- ( CASE WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) >
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
<
30000
THEN ( CASE
WHEN [System_type_id] IN (
35
,
34
,
99
)
THEN
16
ELSE
24
END )
--
24
WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) >
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
>
30000
THEN ( CASE
WHEN [System_type_id] IN (
35
,
34
,
99
)
THEN
16
ELSE
24
END )
--
24
WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) <
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
<
30000
THEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
WHEN CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
)))) <
30000
AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
( ( leaf_offset
* -
1
) -
1
) )
-
1
,
2
)))),
0
),
[varColumnStart])
>
30000
THEN POWER(
2
,
15
)
+ CONVERT(INT, CONVERT(BINARY(
2
), REVERSE(SUBSTRING([ColumnOffsetArray],
(
2
*
leaf_offset
* -
1
) -
1
,
2
))))
- ISNULL(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
)))) >
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 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
)))) <
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 hex_Value ,
[Slot ID] ,
0
FROM @DeletedRecords A
INNER 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
=
2
AND partitions.partition_id
=
allocunits.container_id
)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id
=
partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id
=
partitions.object_id
AND syscolumns.colid
=
cols.partition_column_id
WHERE leaf_offset
<
0
UNION
/*
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
1
ISNULL(SUM(CASE WHEN C.leaf_offset
>
1
THEN max_length
ELSE
0
END),
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
AS [Column value Size] ,
syscolumns.length AS [Column Length] ,
CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit,
1
) =
1
THEN NULL
ELSE SUBSTRING(Rowlogcontents,
( SELECT TOP
1
ISNULL(SUM(CASE
WHEN C.leaf_offset
>
1
AND C.leaf_bit_position
=
0
THEN max_length
ELSE
0
END),
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)
END AS hex_Value ,
[Slot ID] ,
0
FROM @DeletedRecords A
INNER 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
=
2
AND partitions.partition_id
=
allocunits.container_id
)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id
=
partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id
=
partitions.object_id
AND syscolumns.colid
=
cols.partition_column_id
WHERE leaf_offset
>
0
ORDER BY nullbit
DECLARE @BitColumnByte AS INT
SELECT @BitColumnByte
= CONVERT(INT, CEILING(COUNT(*) /
8.0
))
FROM @ColumnNameAndData
WHERE [System_Type_id]
=
104
;
WITH 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
),
CTE
AS ( SELECT RowLogContents ,
[nullbit] ,
[BitMap]
= CONVERT(VARBINARY(
1
), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT
'
,
'
+
( CASE
WHEN [ID]
=
0
THEN CONVERT(NVARCHAR(
1
), ( SUBSTRING(hex_Value,
n,
1
) %
2
))
ELSE CONVERT(NVARCHAR(
1
), ( ( SUBSTRING(hex_Value,
n,
1
)
/
[Bitvalue] )
%
2
))
END )
--
as
[nullBitMap]
FROM
N4 AS Nums
JOIN @ColumnNameAndData
AS C ON n
<=
@BitColumnByte
AND [System_Type_id]
=
104
AND bitpos
=
0
CROSS JOIN @bitTable
WHERE
C.[RowLogContents]
=
D.[RowLogContents]
ORDER BY [RowLogContents] ,
n ASC
FOR
XML
PATH(
''
)
),
1
,
1
,
''
),
'
,
'
,
''
) ),
bitpos
+
1
,
1
)))
FROM @ColumnNameAndData D
WHERE [System_Type_id]
=
104
)
UPDATE A
SET [hex_Value]
=
[BitMap]
FROM @ColumnNameAndData A
INNER JOIN CTE B ON A.[RowLogContents]
=
B.[RowLogContents]
AND A.[nullbit]
=
B.[nullbit]
/*
*************Check for BLOB DATA TYPES*****************************
*/
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @CurrentLSN INT
DECLARE @LinkID INT
DECLARE @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 CURSOR
FOR
/*
We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No, Page ID & AllocUnit ID
*/
SELECT LTRIM(RTRIM(REPLACE([Description],
'
Deallocated
'
,
''
))) AS [PAGE ID] ,
[Slot ID] ,
[AllocUnitId] ,
NULL AS [RowLog Contents
0
] ,
NULL AS [RowLog Contents
0
] ,
Context
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 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 sys.fn_dblog(NULL, NULL)
WHERE Context IN (
'
LCX_NULL
'
)
AND Operation IN (
'
LOP_BEGIN_XACT
'
)
AND [Transaction Name]
=
'
DELETE
'
AND CONVERT(NVARCHAR(
11
), [Begin Time]) BETWEEN @Date_From
AND
@Date_To )
GROUP BY [Description] ,
[Slot ID] ,
[AllocUnitId] ,
Context
UNION
SELECT [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
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_TEXT_MIX
'
)
AND Operation IN (
'
LOP_DELETE_ROWS
'
)
/*
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]
=
'
DELETE
'
AND CONVERT(NVARCHAR(
11
), [Begin Time]) BETWEEN @Date_From
AND
@Date_To )
/*
**************************************
*/
OPEN Page_Data_Cursor
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,
@AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context
WHILE @@FETCH_STATUS
=
0
BEGIN
DECLARE @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 ID
SET @hex_pageid
=
'
0x
'
+
SUBSTRING(@ConsolidatedPageID,
CHARINDEX(
'
:
'
,
@ConsolidatedPageID)
+
1
, LEN(@ConsolidatedPageID)) ---
Seperate the page ID
SELECT @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 integer
FROM ( SELECT CASE SUBSTRING(@hex_pageid,
1
,
2
)
WHEN
'
0x
'
THEN
3
ELSE
0
END
) AS t ( pos )
IF @Context
=
'
LCX_PFS
'
BEGIN
DELETE @temppagedata
INSERT INTO @temppagedata
EXEC
(
'
DBCC PAGE(
'
+ @DataBase_Name +
'
,
'
+ @fileid +
'
,
'
+
@pageid
+
'
, 1) with tableresults,no_infomsgs;
'
);
INSERT INTO @pagedata
SELECT @ConsolidatedPageID ,
@fileid ,
@pageid ,
@AllocUnitID ,
[ParentObject] ,
[Object] ,
[Field] ,
[Value]
FROM @temppagedata
END
ELSE
IF @Context
=
'
LCX_TEXT_MIX
'
BEGIN
INSERT INTO @ModifiedRawData
SELECT @ConsolidatedPageID ,
@fileid ,
@pageid ,
@Slotid ,
@AllocUnitID ,
NULL ,
0
,
CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX,
11
,
2
)))) ,
@LCX_TEXT_MIX ,
@LinkID ,
0
END
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,
@AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @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
format
INSERT 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(( SELECT
REPLACE(SUBSTRING([Value],
CHARINDEX(
'
:
'
,
[Value])
+
1
,
CHARINDEX(
'
†
'
,
[Value])
- CHARINDEX(
'
:
'
,
[Value])),
'
†
'
,
''
)
FROM @pagedata C
WHERE 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
)
FOR
XML PATH(
''
)
),
1
,
1
,
''
),
'
'
,
''
)
),
1
,
20000
) AS [Value] ,
SUBSTRING(( SELECT
'
0x
'
+
REPLACE(STUFF(( SELECT
REPLACE(SUBSTRING([Value],
CHARINDEX(
'
:
'
,
[Value])
+
1
,
CHARINDEX(
'
†
'
,
[Value])
- CHARINDEX(
'
:
'
,
[Value])),
'
†
'
,
''
)
FROM
@pagedata C
WHERE
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
)
FOR
XML PATH(
''
)
),
1
,
1
,
''
),
'
'
,
''
)
),
7
,
4
) AS [Length]
FROM @pagedata B
WHERE [Object] LIKE
'
%Memory Dump%
'
GROUP BY [Page ID] ,
[FILE IDS] ,
[PAGE IDS] ,
[ParentObject] ,
[AllocUnitId]
--
,[Current LSN]
ORDER BY [Slot ID]
UPDATE @ModifiedRawData
SET [RowLog Len]
= CONVERT(VARBINARY(
8000
), REVERSE(CAST(
''
AS XML).value(
'
xs:hexBinary(substring(sql:column("[RowLog Length]"),0))
'
,
'
varbinary(Max)
'
)))
FROM @ModifiedRawData
WHERE [LINK ID]
=
0
UPDATE @ModifiedRawData
SET [RowLog Contents
0
] = CAST(
''
AS XML).value(
'
xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))
'
,
'
varbinary(Max)
'
)
FROM @ModifiedRawData
WHERE [LINK ID]
=
0
UPDATE B
SET B.[RowLog Contents
0
] = ( CASE WHEN A.[RowLog Contents
0
] IS NOT NULL
AND C.[RowLog Contents
0
] IS NOT NULL
THEN A.[RowLog Contents
0
]
+ C.[RowLog Contents
0
]
WHEN A.[RowLog Contents
0
] IS NULL
AND C.[RowLog Contents
0
] IS NOT NULL
THEN C.[RowLog Contents
0
]
WHEN A.[RowLog Contents
0
] IS NOT NULL
AND C.[RowLog Contents
0
] IS NULL
THEN A.[RowLog Contents
0
]
END ) ,
B.[Update]
= ISNULL(B.[Update],
0
) +
1
FROM @ModifiedRawData B
LEFT 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 NULL
OR C.[RowLog Contents
0
] IS NOT NULL
)
UPDATE B
SET B.[RowLog Contents
0
] = ( CASE WHEN A.[RowLog Contents
0
] IS NOT NULL
AND C.[RowLog Contents
0
] IS NOT NULL
THEN A.[RowLog Contents
0
]
+ C.[RowLog Contents
0
]
WHEN A.[RowLog Contents
0
] IS NULL
AND C.[RowLog Contents
0
] IS NOT NULL
THEN C.[RowLog Contents
0
]
WHEN A.[RowLog Contents
0
] IS NOT NULL
AND C.[RowLog Contents
0
] IS NULL
THEN A.[RowLog Contents
0
]
END )
--,B.[Update]=ISNULL(B.[Update],
0
)+
1
FROM @ModifiedRawData B
LEFT 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]
=
0
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]
AND B.[Update]
=
0
WHERE ( A.[RowLog Contents
0
] IS NOT NULL
OR C.[RowLog Contents
0
] IS NOT NULL
)
UPDATE @ModifiedRawData
SET [RowLog Contents
0
] = ( CASE WHEN [RowLog Len] >=
8000
THEN SUBSTRING([RowLog Contents
0
],
15
, [RowLog Len])
WHEN [RowLog Len]
<
8000
THEN SUBSTRING([RowLog Contents
0
],
15
+
6
,
CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents
0
],
15
,
6
)))))
END )
FROM @ModifiedRawData
WHERE [LINK ID]
=
0
UPDATE @ColumnNameAndData
SET [hex_Value]
= [RowLog Contents
0
]
--,A.[Update]=A.[Update]+
1
FROM @ColumnNameAndData A
INNER 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 @ColumnNameAndData
SET [hex_Value]
= ( CASE WHEN B.[RowLog Contents
0
] IS NOT NULL
AND C.[RowLog Contents
0
] IS NOT NULL
THEN B.[RowLog Contents
0
]
+ C.[RowLog Contents
0
]
WHEN B.[RowLog Contents
0
] IS NULL
AND C.[RowLog Contents
0
] IS NOT NULL
THEN C.[RowLog Contents
0
]
WHEN B.[RowLog Contents
0
] IS NOT NULL
AND C.[RowLog Contents
0
] IS NULL
THEN B.[RowLog Contents
0
]
END )
--,A.[Update]=A.[Update]+
1
FROM @ColumnNameAndData A
LEFT JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
5
,
4
)))) =
B.[PAGE IDS]
AND B.[Link ID]
=
0
LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],
17
,
4
)))) =
C.[PAGE IDS]
AND C.[Link ID]
=
0
WHERE [System_Type_Id] IN (
99
,
167
,
175
,
231
,
239
,
241
,
165
,
98
)
AND ( B.[RowLog Contents
0
] IS NOT NULL
OR C.[RowLog Contents
0
] IS NOT NULL
)
UPDATE @ColumnNameAndData
SET [hex_Value]
= [RowLog Contents
0
]
--,A.[Update]=A.[Update]+
1
FROM @ColumnNameAndData A
INNER 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 @ColumnNameAndData
SET [hex_Value]
= [RowLog Contents
0
]
--,A.[Update]=A.[Update]+
10
FROM @ColumnNameAndData A
INNER 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]
=
0
UPDATE @ColumnNameAndData
SET [hex_Value]
= [RowLog Contents
0
]
--,A.[Update]=A.[Update]+
1
FROM @ColumnNameAndData A
INNER 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]
=
0
UPDATE @ColumnNameAndData
SET [hex_value]
=
0xFFFE
+ SUBSTRING([hex_value],
9
, LEN([hex_value]))
--,[Update]=[Update]+
1
WHERE [system_type_id]
=
241
CREATE TABLE [#temp_Data]
(
[FieldName] VARCHAR(MAX) ,
[FieldValue] NVARCHAR(MAX) ,
[Rowlogcontents] VARBINARY(
8000
) ,
[Row ID] INT
)
INSERT INTO #temp_Data
SELECT NAME ,
CASE WHEN system_type_id IN (
231
,
239
)
THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value)))
--
NVARCHAR ,NCHAR
WHEN system_type_id IN (
167
,
175
)
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value)))
--
VARCHAR,CHAR
WHEN system_type_id IN (
35
)
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value)))
--
Text
WHEN system_type_id IN (
99
)
THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value)))
--
nText
WHEN system_type_id
=
48
THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(
1
), REVERSE(hex_Value)))) --
TINY INTEGER
WHEN system_type_id
=
52
THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(
2
), REVERSE(hex_Value)))) --
SMALL INTEGER
WHEN system_type_id
=
56
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(
4
), REVERSE(hex_Value)))) --
INTEGER
WHEN system_type_id
=
127
THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(
8
), REVERSE(hex_Value))))--
BIG INTEGER
WHEN system_type_id
=
61
THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(
8000
), REVERSE(hex_Value))),
100
) --
DATETIME
WHEN system_type_id
=
58
THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(
8000
), REVERSE(hex_Value))),
100
) --
SMALL DATETIME
WHEN system_type_id
=
108
THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(
38
,
20
), CONVERT(VARBINARY, CONVERT(VARBINARY(
1
), xprec)
+ CONVERT(VARBINARY(
1
), xscale))
+ CONVERT(VARBINARY(
1
),
0
) + hex_Value)) ---
NUMERIC
WHEN system_type_id
=
106
THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(
38
,
20
), CONVERT(VARBINARY, CONVERT(VARBINARY(
1
), xprec)
+ CONVERT(VARBINARY(
1
), xscale))
+ CONVERT(VARBINARY(
1
),
0
) + hex_Value)) ---
DECIMAL
WHEN system_type_id IN (
60
,
122
)
THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(
8000
), REVERSE(hex_Value))),
2
) --
MONEY,SMALLMONEY
WHEN system_type_id
=
104
THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(
1
), hex_Value)
%
2
)) --
BIT
WHEN system_type_id
=
62
THEN 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))))
---
FLOAT
WHEN system_type_id
=
59
THEN 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
) --
Real
WHEN system_type_id IN (
165
,
173
)
THEN ( CASE WHEN CHARINDEX(0x,
CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
VARBINARY(8000)
'
)) =
0
THEN
'
0x
'
ELSE
''
END )
+ CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
varchar(max)
'
) --
BINARY,VARBINARY
WHEN system_type_id
=
34
THEN ( CASE WHEN CHARINDEX(0x,
CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
VARBINARY(8000)
'
)) =
0
THEN
'
0x
'
ELSE
''
END )
+ CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
varchar(max)
'
) --
IMAGE
WHEN system_type_id
=
36
THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value))
--
UNIQUEIDENTIFIER
WHEN system_type_id
=
231
THEN CONVERT(VARCHAR(MAX), CONVERT(SYSNAME, hex_Value))
--
SYSNAME
WHEN system_type_id
=
241
THEN CONVERT(VARCHAR(MAX), CONVERT(XML, hex_Value))
--
XML
WHEN system_type_id
=
189
THEN ( CASE WHEN CHARINDEX(0x,
CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
VARBINARY(8000)
'
)) =
0
THEN
'
0x
'
ELSE
''
END )
+ CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
varchar(max)
'
) --
TIMESTAMP
WHEN system_type_id
=
98
THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value,
1
,
1
)) =
56
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(
4
), REVERSE(SUBSTRING(hex_Value,
3
,
LEN(hex_Value))))))
--
INTEGER
WHEN CONVERT(INT, SUBSTRING(hex_Value,
1
,
1
)) =
108
THEN 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))))
---
NUMERIC
WHEN CONVERT(INT, SUBSTRING(hex_Value,
1
,
1
)) =
167
THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,
9
,
LEN(hex_Value)))))
--
VARCHAR,CHAR
WHEN CONVERT(INT, SUBSTRING(hex_Value,
1
,
1
)) =
36
THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),
3
,
20
))) --
UNIQUEIDENTIFIER
WHEN CONVERT(INT, SUBSTRING(hex_Value,
1
,
1
)) =
61
THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(
8000
), REVERSE(SUBSTRING(hex_Value,
3
,
LEN(hex_Value))))),
100
) --
DATETIME
WHEN CONVERT(INT, SUBSTRING(hex_Value,
1
,
1
)) =
165
THEN
'
0x
'
+
SUBSTRING(( CASE WHEN CHARINDEX(0x,
CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
VARBINARY(8000)
'
)) =
0
THEN
'
0x
'
ELSE
''
END )
+ CAST(
''
AS XML).value(
'
xs:hexBinary(sql:column("hex_Value"))
'
,
'
varchar(max)
'
),
11
, LEN(hex_Value)) --
BINARY,VARBINARY
END )
END AS FieldValue ,
[Rowlogcontents] ,
[Row ID]
FROM @ColumnNameAndData
ORDER 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 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 @sql
GO
---执行存储过程两个参数（数据库名，表名）
EXEC Recover_Deleted_Data_Proc
'
数据库名称
'
,
'
dbo.tableName
'--表名前面要加dbo 不然会报错的。
--执行存储过程四个参数（数据库名称，表明，开始时间，结束时间【按照时间段来恢复】）
EXEC Recover_Deleted_Data_Proc
'
数据库名称
'
,
'
dbo.TableName
'
,
'
2014-04-23
'
,
'
2014-04-23
'--表名要加dbo。
转自http://www.cnblogs.com/lyhabc/p/3683147.html#2924733.
