原文地址：https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
在我的SQLServer的工作中，最经常被问到的一个问题就是“能恢复删除的数据吗？”
我的回答是肯定的，注意下面的数据类型是可以通过脚本直接恢复的，当然数据库的版本要在SQLServer2005 以上才行。
经过讨论发现2008和2012以及2014的express版本也不能实现脚本直接恢复。
- 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
用一个例子演示一下整个过程：
1. 首先创建一个存储过程来将删除数据查询出来，也是由原文作者开发的如下：
1 -- Script Name: Recover_Deleted_Data_Proc 2 -- Script Type : Recovery Procedure 3 -- Develop By: Muhammad Imran 4 -- Date Created: 15 Oct 2011 5 -- Modify Date: 22 Aug 2012 6 -- Version : 3.1 7 -- Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS. 8 9 10 CREATE PROCEDURE Recover_Deleted_Data_Proc 11 @Database_Name NVARCHAR(MAX) , 12 @SchemaName_n_TableName NVARCHAR(MAX) , 13 @Date_From DATETIME = '1900/01/01' , 14 @Date_To DATETIME = '9999/12/31' 15 AS 16 DECLARE @RowLogContents VARBINARY(8000) 17 DECLARE @TransactionID NVARCHAR(MAX) 18 DECLARE @AllocUnitID BIGINT 19 DECLARE @AllocUnitName NVARCHAR(MAX) 20 DECLARE @SQL NVARCHAR(MAX) 21 DECLARE @Compatibility_Level INT 22 23 24 SELECT @Compatibility_Level = dtb.compatibility_level 25 FROM master.sys.databases AS dtb 26 WHERE dtb.name = @Database_Name 27 28 IF ISNULL(@Compatibility_Level, 0) <= 80 29 BEGIN 30 RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1) 31 RETURN 32 END 33 34 IF ( SELECT COUNT(*) 35 FROM INFORMATION_SCHEMA.TABLES 36 WHERE [TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName 37 ) = 0 38 BEGIN 39 RAISERROR('Could not found the table in the defined database',16,1) 40 RETURN 41 END 42 43 DECLARE @bitTable TABLE 44 ( 45 [ID] INT , 46 [Bitvalue] INT 47 ) 48 --Create table to set the bit position of one byte. 49 50 INSERT INTO @bitTable 51 SELECT 0 , 52 2 53 UNION ALL 54 SELECT 1 , 55 2 56 UNION ALL 57 SELECT 2 , 58 4 59 UNION ALL 60 SELECT 3 , 61 8 62 UNION ALL 63 SELECT 4 , 64 16 65 UNION ALL 66 SELECT 5 , 67 32 68 UNION ALL 69 SELECT 6 , 70 64 71 UNION ALL 72 SELECT 7 , 73 128 74 75 --Create table to collect the row data. 76 DECLARE @DeletedRecords TABLE 77 ( 78 [Row ID] INT IDENTITY(1, 1) , 79 [RowLogContents] VARBINARY(8000) , 80 [AllocUnitID] BIGINT , 81 [Transaction ID] NVARCHAR(MAX) , 82 [FixedLengthData] SMALLINT , 83 [TotalNoOfCols] SMALLINT , 84 [NullBitMapLength] SMALLINT , 85 [NullBytes] VARBINARY(8000) , 86 [TotalNoofVarCols] SMALLINT , 87 [ColumnOffsetArray] VARBINARY(8000) , 88 [VarColumnStart] SMALLINT , 89 [Slot ID] INT , 90 [NullBitMap] VARCHAR(MAX) 91 ) 92 --Create a common table expression to get all the row data plus how many bytes we have for each row. 93 ; 94 WITH RowData 95 AS ( SELECT [RowLog Contents 0] AS [RowLogContents] , 96 [AllocUnitID] AS [AllocUnitID] , 97 [Transaction ID] AS [Transaction ID] 98 99 --[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes) 100 , 101 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 102 2 + 1, 2)))) AS [FixedLengthData] --@FixedLengthData 103 104 -- [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes) 105 , 106 CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 107 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 108 2 + 1, 2)))) + 1, 109 2)))) AS [TotalNoOfCols] 110 111 --[NullBitMapLength]=ceiling([Total No of Columns] /8.0) 112 , 113 CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 114 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 115 2 + 1, 2)))) + 1, 116 2)))) / 8.0)) AS [NullBitMapLength] 117 118 --[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] ) 119 , 120 SUBSTRING([RowLog Contents 0], 121 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 122 2 + 1, 2)))) + 3, 123 CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 124 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 125 2 + 1, 2)))) + 1, 126 2)))) / 8.0))) AS [NullBytes] 127 128 --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 ) 129 , 130 ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 131 0x10, 0x30, 0x70 ) 132 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 133 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 134 2 + 1, 2)))) + 3 135 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 136 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 137 2 + 1, 2)))) + 1, 138 2)))) / 8.0)), 2)))) 139 ELSE NULL 140 END ) AS [TotalNoofVarCols] 141 142 --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 ) 143 , 144 ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 145 0x10, 0x30, 0x70 ) 146 THEN SUBSTRING([RowLog Contents 0], 147 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 148 2 + 1, 2)))) + 3 149 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 150 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 151 2 + 1, 2)))) + 1, 152 2)))) / 8.0)) 153 + 2, 154 ( CASE WHEN SUBSTRING([RowLog Contents 0], 155 1, 1) IN ( 0x10, 156 0x30, 0x70 ) 157 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 158 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 159 2 + 1, 2)))) + 3 160 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 161 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 162 2 + 1, 2)))) + 1, 163 2)))) / 8.0)), 2)))) 164 ELSE NULL 165 END ) * 2) 166 ELSE NULL 167 END ) AS [ColumnOffsetArray] 168 169 -- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2) 170 , 171 CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 172 0x10, 0x30, 0x70 ) 173 THEN ( CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 174 2 + 1, 2)))) + 4 175 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 176 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 177 2 + 1, 2)))) + 1, 178 2)))) / 8.0)) 179 + ( ( CASE WHEN SUBSTRING([RowLog Contents 0], 180 1, 1) IN ( 0x10, 181 0x30, 0x70 ) 182 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 183 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 184 2 + 1, 2)))) + 3 185 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 186 CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 187 2 + 1, 2)))) + 1, 188 2)))) / 8.0)), 2)))) 189 ELSE NULL 190 END ) * 2 ) ) 191 ELSE NULL 192 END AS [VarColumnStart] , 193 [Slot ID] 194 FROM sys.fn_dblog(NULL, NULL) 195 WHERE AllocUnitId IN ( 196 SELECT [Allocation_unit_id] 197 FROM sys.allocation_units allocunits 198 INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 199 1, 3 ) 200 AND partitions.hobt_id = allocunits.container_id 201 ) 202 OR ( allocunits.type = 2 203 AND partitions.partition_id = allocunits.container_id 204 ) 205 WHERE object_id = OBJECT_ID('' 206 + @SchemaName_n_TableName 207 + '') ) 208 AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' ) 209 AND Operation IN ( 'LOP_DELETE_ROWS' ) 210 AND SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10, 211 0x30, 0x70 ) 212 213 /*Use this subquery to filter the date*/ 214 AND [TRANSACTION ID] IN ( 215 SELECT DISTINCT 216 [TRANSACTION ID] 217 FROM sys.fn_dblog(NULL, NULL) 218 WHERE Context IN ( 'LCX_NULL' ) 219 AND Operation IN ( 'LOP_BEGIN_XACT' ) 220 AND [Transaction Name] IN ( 'DELETE', 221 'user_transaction' ) 222 AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From 223 AND 224 @Date_To ) 225 ), 226 227 --Use this technique to repeate the row till the no of bytes of the row. 228 N1 ( n ) 229 AS ( SELECT 1 230 UNION ALL 231 SELECT 1 232 ), 233 N2 ( n ) 234 AS ( SELECT 1 235 FROM N1 AS X , 236 N1 AS Y 237 ), 238 N3 ( n ) 239 AS ( SELECT 1 240 FROM N2 AS X , 241 N2 AS Y 242 ), 243 N4 ( n ) 244 AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n ) 245 FROM N3 AS X , 246 N3 AS Y 247 ) 248 INSERT INTO @DeletedRecords 249 SELECT RowLogContents , 250 [AllocUnitID] , 251 [Transaction ID] , 252 [FixedLengthData] , 253 [TotalNoOfCols] , 254 [NullBitMapLength] , 255 [NullBytes] , 256 [TotalNoofVarCols] , 257 [ColumnOffsetArray] , 258 [VarColumnStart] , 259 [Slot ID] 260 ---Get the Null value against each column (1 means null zero means not null) 261 , 262 [NullBitMap] = ( REPLACE(STUFF(( SELECT 263 ',' 264 + ( CASE 265 WHEN [ID] = 0 266 THEN CONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes, 267 n, 1) % 2 )) 268 ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes, 269 n, 1) 270 / [Bitvalue] ) 271 % 2 )) 272 END ) --as [nullBitMap] 273 FROM N4 AS Nums 274 JOIN RowData AS C ON n <= NullBitMapLength 275 CROSS JOIN @bitTable 276 WHERE 277 C.[RowLogContents] = D.[RowLogContents] 278 ORDER BY [RowLogContents] , 279 n ASC 280 FOR 281 XML PATH('') 282 ), 1, 1, ''), ',', '') ) 283 FROM RowData D 284 285 IF ( SELECT COUNT(*) 286 FROM @DeletedRecords 287 ) = 0 288 BEGIN 289 RAISERROR('There is no data in the log as per the search criteria',16,1) 290 RETURN 291 END 292 293 DECLARE @ColumnNameAndData TABLE 294 ( 295 [Row ID] INT , 296 [Rowlogcontents] VARBINARY(MAX) , 297 [NAME] SYSNAME , 298 [nullbit] SMALLINT , 299 [leaf_offset] SMALLINT , 300 [length] SMALLINT , 301 [system_type_id] TINYINT , 302 [bitpos] TINYINT , 303 [xprec] TINYINT , 304 [xscale] TINYINT , 305 [is_null] INT , 306 [Column value Size] INT , 307 [Column Length] INT , 308 [hex_Value] VARBINARY(MAX) , 309 [Slot ID] INT , 310 [Update] INT 311 ) 312 313 --Create common table expression and join it with the rowdata table 314 -- to get each column details 315 /*This part is for variable data columns*/ 316 --@RowLogContents, 317 --(col.columnOffValue - col.columnLength) + 1, 318 --col.columnLength 319 --) 320 INSERT INTO @ColumnNameAndData 321 SELECT [Row ID] , 322 Rowlogcontents , 323 NAME , 324 cols.leaf_null_bit AS nullbit , 325 leaf_offset , 326 ISNULL(syscolumns.length, cols.max_length) AS [length] , 327 cols.system_type_id , 328 cols.leaf_bit_position AS bitpos , 329 ISNULL(syscolumns.xprec, cols.precision) AS xprec , 330 ISNULL(syscolumns.xscale, cols.scale) AS xscale , 331 SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null , 332 ( CASE WHEN leaf_offset < 1 333 AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 334 1) = 0 335 THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 336 ( 2 337 * leaf_offset 338 * -1 ) - 1, 2)))) > 30000 339 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 340 ( 2 341 * leaf_offset 342 * -1 ) - 1, 2)))) 343 - POWER(2, 15) 344 ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 345 ( 2 346 * leaf_offset 347 * -1 ) - 1, 2)))) 348 END ) 349 END ) AS [Column value Size] , 350 ( CASE WHEN leaf_offset < 1 351 AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 352 1) = 0 353 THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 354 ( 2 355 * leaf_offset 356 * -1 ) - 1, 2)))) > 30000 357 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 358 ( 2 359 * ( ( leaf_offset 360 * -1 ) - 1 ) ) 361 - 1, 2)))), 0), 362 [varColumnStart]) < 30000 363 THEN ( CASE WHEN [System_type_id] IN ( 364 35, 34, 99 ) THEN 16 365 ELSE 24 366 END ) 367 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 368 ( 2 369 * leaf_offset 370 * -1 ) - 1, 2)))) > 30000 371 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 372 ( 2 373 * ( ( leaf_offset 374 * -1 ) - 1 ) ) 375 - 1, 2)))), 0), 376 [varColumnStart]) > 30000 377 THEN ( CASE WHEN [System_type_id] IN ( 378 35, 34, 99 ) THEN 16 379 ELSE 24 380 END ) --24 381 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 382 ( 2 383 * leaf_offset 384 * -1 ) - 1, 2)))) < 30000 385 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 386 ( 2 387 * ( ( leaf_offset 388 * -1 ) - 1 ) ) 389 - 1, 2)))), 0), 390 [varColumnStart]) < 30000 391 THEN ( CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 392 ( 2 393 * leaf_offset 394 * -1 ) - 1, 2)))) 395 - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 396 ( 2 397 * ( ( leaf_offset 398 * -1 ) - 1 ) ) 399 - 1, 2)))), 0), 400 [varColumnStart]) ) 401 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 402 ( 2 403 * leaf_offset 404 * -1 ) - 1, 2)))) < 30000 405 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 406 ( 2 407 * ( ( leaf_offset 408 * -1 ) - 1 ) ) 409 - 1, 2)))), 0), 410 [varColumnStart]) > 30000 411 THEN POWER(2, 15) 412 + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 413 ( 2 414 * leaf_offset 415 * -1 ) - 1, 2)))) 416 - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 417 ( 2 418 * ( ( leaf_offset 419 * -1 ) - 1 ) ) 420 - 1, 2)))), 0), 421 [varColumnStart]) 422 END ) 423 END ) AS [Column Length] , 424 ( CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1 425 THEN NULL 426 ELSE SUBSTRING(Rowlogcontents, 427 ( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 428 ( 2 429 * leaf_offset 430 * -1 ) - 1, 2)))) > 30000 431 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 432 ( 2 433 * leaf_offset 434 * -1 ) - 1, 2)))) 435 - POWER(2, 15) 436 ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 437 ( 2 438 * leaf_offset 439 * -1 ) - 1, 2)))) 440 END ) 441 - ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 442 ( 2 443 * leaf_offset 444 * -1 ) - 1, 2)))) > 30000 445 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 446 ( 2 447 * ( ( leaf_offset 448 * -1 ) - 1 ) ) 449 - 1, 2)))), 0), 450 [varColumnStart]) < 30000 451 THEN ( CASE 452 WHEN [System_type_id] IN ( 453 35, 34, 99 ) 454 THEN 16 455 ELSE 24 456 END ) --24 457 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 458 ( 2 459 * leaf_offset 460 * -1 ) - 1, 2)))) > 30000 461 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 462 ( 2 463 * ( ( leaf_offset 464 * -1 ) - 1 ) ) 465 - 1, 2)))), 0), 466 [varColumnStart]) > 30000 467 THEN ( CASE 468 WHEN [System_type_id] IN ( 469 35, 34, 99 ) 470 THEN 16 471 ELSE 24 472 END ) --24 473 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 474 ( 2 475 * leaf_offset 476 * -1 ) - 1, 2)))) < 30000 477 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 478 ( 2 479 * ( ( leaf_offset 480 * -1 ) - 1 ) ) 481 - 1, 2)))), 0), 482 [varColumnStart]) < 30000 483 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 484 ( 2 485 * leaf_offset 486 * -1 ) - 1, 2)))) 487 - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 488 ( 2 489 * ( ( leaf_offset 490 * -1 ) - 1 ) ) 491 - 1, 2)))), 0), 492 [varColumnStart]) 493 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 494 ( 2 495 * leaf_offset 496 * -1 ) - 1, 2)))) < 30000 497 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 498 ( 2 499 * ( ( leaf_offset 500 * -1 ) - 1 ) ) 501 - 1, 2)))), 0), 502 [varColumnStart]) > 30000 503 THEN POWER(2, 15) 504 + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 505 ( 2 506 * leaf_offset 507 * -1 ) - 1, 2)))) 508 - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 509 ( 2 510 * ( ( leaf_offset 511 * -1 ) - 1 ) ) 512 - 1, 2)))), 0), 513 [varColumnStart]) 514 END ) ) + 1, 515 ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 516 ( 2 517 * leaf_offset 518 * -1 ) - 1, 2)))) > 30000 519 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 520 ( 2 521 * ( ( leaf_offset 522 * -1 ) - 1 ) ) 523 - 1, 2)))), 0), 524 [varColumnStart]) < 30000 525 THEN ( CASE WHEN [System_type_id] IN ( 526 35, 34, 99 ) 527 THEN 16 528 ELSE 24 529 END ) --24 530 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 531 ( 2 532 * leaf_offset 533 * -1 ) - 1, 2)))) > 30000 534 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 535 ( 2 536 * ( ( leaf_offset 537 * -1 ) - 1 ) ) 538 - 1, 2)))), 0), 539 [varColumnStart]) > 30000 540 THEN ( CASE WHEN [System_type_id] IN ( 541 35, 34, 99 ) 542 THEN 16 543 ELSE 24 544 END ) --24 545 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 546 ( 2 547 * leaf_offset 548 * -1 ) - 1, 2)))) < 30000 549 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 550 ( 2 551 * ( ( leaf_offset 552 * -1 ) - 1 ) ) 553 - 1, 2)))), 0), 554 [varColumnStart]) < 30000 555 THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 556 ( 2 557 * leaf_offset 558 * -1 ) - 1, 2)))) 559 - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 560 ( 2 561 * ( ( leaf_offset 562 * -1 ) - 1 ) ) 563 - 1, 2)))), 0), 564 [varColumnStart])) 565 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 566 ( 2 567 * leaf_offset 568 * -1 ) - 1, 2)))) < 30000 569 AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 570 ( 2 571 * ( ( leaf_offset 572 * -1 ) - 1 ) ) 573 - 1, 2)))), 0), 574 [varColumnStart]) > 30000 575 THEN POWER(2, 15) 576 + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 577 ( 2 578 * leaf_offset 579 * -1 ) - 1, 2)))) 580 - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray], 581 ( 2 582 * ( ( leaf_offset 583 * -1 ) - 1 ) ) 584 - 1, 2)))), 0), 585 [varColumnStart]) 586 END )) 587 END ) AS hex_Value , 588 [Slot ID] , 589 0 590 FROM @DeletedRecords A 591 INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id] 592 INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 593 1, 3 ) 594 AND partitions.hobt_id = allocunits.container_id 595 ) 596 OR ( allocunits.type = 2 597 AND partitions.partition_id = allocunits.container_id 598 ) 599 INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id 600 LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id 601 AND syscolumns.colid = cols.partition_column_id 602 WHERE leaf_offset < 0 603 UNION 604 /*This part is for fixed data columns*/ 605 SELECT [Row ID] , 606 Rowlogcontents , 607 NAME , 608 cols.leaf_null_bit AS nullbit , 609 leaf_offset , 610 ISNULL(syscolumns.length, cols.max_length) AS [length] , 611 cols.system_type_id , 612 cols.leaf_bit_position AS bitpos , 613 ISNULL(syscolumns.xprec, cols.precision) AS xprec , 614 ISNULL(syscolumns.xscale, cols.scale) AS xscale , 615 SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null , 616 ( SELECT TOP 1 617 ISNULL(SUM(CASE WHEN C.leaf_offset > 1 618 THEN max_length 619 ELSE 0 620 END), 0) 621 FROM sys.system_internals_partition_columns C 622 WHERE cols.partition_id = C.partition_id 623 AND C.leaf_null_bit < cols.leaf_null_bit 624 ) + 5 AS [Column value Size] , 625 syscolumns.length AS [Column Length] , 626 CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1 627 THEN NULL 628 ELSE SUBSTRING(Rowlogcontents, 629 ( SELECT TOP 1 630 ISNULL(SUM(CASE 631 WHEN C.leaf_offset > 1 632 AND C.leaf_bit_position = 0 633 THEN max_length 634 ELSE 0 635 END), 0) 636 FROM sys.system_internals_partition_columns C 637 WHERE cols.partition_id = C.partition_id 638 AND C.leaf_null_bit < cols.leaf_null_bit 639 ) + 5, syscolumns.length) 640 END AS hex_Value , 641 [Slot ID] , 642 0 643 FROM @DeletedRecords A 644 INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id] 645 INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 646 1, 3 ) 647 AND partitions.hobt_id = allocunits.container_id 648 ) 649 OR ( allocunits.type = 2 650 AND partitions.partition_id = allocunits.container_id 651 ) 652 INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id 653 LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id 654 AND syscolumns.colid = cols.partition_column_id 655 WHERE leaf_offset > 0 656 ORDER BY nullbit 657 658 DECLARE @BitColumnByte AS INT 659 SELECT @BitColumnByte = CONVERT(INT, CEILING(COUNT(*) / 8.0)) 660 FROM @ColumnNameAndData 661 WHERE [System_Type_id] = 104; 662 WITH N1 ( n ) 663 AS ( SELECT 1 664 UNION ALL 665 SELECT 1 666 ), 667 N2 ( n ) 668 AS ( SELECT 1 669 FROM N1 AS X , 670 N1 AS Y 671 ), 672 N3 ( n ) 673 AS ( SELECT 1 674 FROM N2 AS X , 675 N2 AS Y 676 ), 677 N4 ( n ) 678 AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n ) 679 FROM N3 AS X , 680 N3 AS Y 681 ), 682 CTE 683 AS ( SELECT RowLogContents , 684 [nullbit] , 685 [BitMap] = CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT 686 ',' 687 + ( CASE 688 WHEN [ID] = 0 689 THEN CONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value, 690 n, 1) % 2 )) 691 ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value, 692 n, 1) 693 / [Bitvalue] ) 694 % 2 )) 695 END ) --as [nullBitMap] 696 FROM 697 N4 AS Nums 698 JOIN @ColumnNameAndData 699 AS C ON n <= @BitColumnByte 700 AND [System_Type_id] = 104 701 AND bitpos = 0 702 CROSS JOIN @bitTable 703 WHERE 704 C.[RowLogContents] = D.[RowLogContents] 705 ORDER BY [RowLogContents] , 706 n ASC 707 FOR 708 XML 709 PATH('') 710 ), 1, 1, ''), 711 ',', '') ), 712 bitpos + 1, 1))) 713 FROM @ColumnNameAndData D 714 WHERE [System_Type_id] = 104 715 ) 716 UPDATE A 717 SET [hex_Value] = [BitMap] 718 FROM @ColumnNameAndData A 719 INNER JOIN CTE B ON A.[RowLogContents] = B.[RowLogContents] 720 AND A.[nullbit] = B.[nullbit] 721 722 723 /**************Check for BLOB DATA TYPES******************************/ 724 DECLARE @Fileid INT 725 DECLARE @Pageid INT 726 DECLARE @Slotid INT 727 DECLARE @CurrentLSN INT 728 DECLARE @LinkID INT 729 DECLARE @Context VARCHAR(50) 730 DECLARE @ConsolidatedPageID VARCHAR(MAX) 731 DECLARE @LCX_TEXT_MIX VARBINARY(MAX) 732 733 DECLARE @temppagedata TABLE 734 ( 735 [ParentObject] SYSNAME , 736 [Object] SYSNAME , 737 [Field] SYSNAME , 738 [Value] SYSNAME 739 ) 740 741 DECLARE @pagedata TABLE 742 ( 743 [Page ID] SYSNAME , 744 [File IDS] INT , 745 [Page IDS] INT , 746 [AllocUnitId] BIGINT , 747 [ParentObject] SYSNAME , 748 [Object] SYSNAME , 749 [Field] SYSNAME , 750 [Value] SYSNAME 751 ) 752 753 DECLARE @ModifiedRawData TABLE 754 ( 755 [ID] INT IDENTITY(1, 1) , 756 [PAGE ID] VARCHAR(MAX) , 757 [FILE IDS] INT , 758 [PAGE IDS] INT , 759 [Slot ID] INT , 760 [AllocUnitId] BIGINT , 761 [RowLog Contents 0_var] VARCHAR(MAX) , 762 [RowLog Length] VARCHAR(50) , 763 [RowLog Len] INT , 764 [RowLog Contents 0] VARBINARY(MAX) , 765 [Link ID] INT DEFAULT ( 0 ) , 766 [Update] INT 767 ) 768 769 DECLARE Page_Data_Cursor CURSOR 770 FOR 771 /*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*/ 772 SELECT LTRIM(RTRIM(REPLACE([Description], 'Deallocated', ''))) AS [PAGE ID] , 773 [Slot ID] , 774 [AllocUnitId] , 775 NULL AS [RowLog Contents 0] , 776 NULL AS [RowLog Contents 0] , 777 Context 778 FROM sys.fn_dblog(NULL, NULL) 779 WHERE AllocUnitId IN ( 780 SELECT [Allocation_unit_id] 781 FROM sys.allocation_units allocunits 782 INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 783 1, 3 ) 784 AND partitions.hobt_id = allocunits.container_id 785 ) 786 OR ( allocunits.type = 2 787 AND partitions.partition_id = allocunits.container_id 788 ) 789 WHERE object_id = OBJECT_ID('' + @SchemaName_n_TableName 790 + '') ) 791 AND Operation IN ( 'LOP_MODIFY_ROW' ) 792 AND [Context] IN ( 'LCX_PFS' ) 793 AND Description LIKE '%Deallocated%' 794 /*Use this subquery to filter the date*/ 795 AND [TRANSACTION ID] IN ( 796 SELECT DISTINCT 797 [TRANSACTION ID] 798 FROM sys.fn_dblog(NULL, NULL) 799 WHERE Context IN ( 'LCX_NULL' ) 800 AND Operation IN ( 'LOP_BEGIN_XACT' ) 801 AND [Transaction Name] = 'DELETE' 802 AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From 803 AND 804 @Date_To ) 805 GROUP BY [Description] , 806 [Slot ID] , 807 [AllocUnitId] , 808 Context 809 UNION 810 SELECT [PAGE ID] , 811 [Slot ID] , 812 [AllocUnitId] , 813 SUBSTRING([RowLog Contents 0], 15, 814 LEN([RowLog Contents 0])) AS [RowLog Contents 0] , 815 CONVERT(INT, SUBSTRING([RowLog Contents 0], 7, 2)) , 816 Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN] 817 FROM sys.fn_dblog(NULL, NULL) 818 WHERE AllocUnitId IN ( 819 SELECT [Allocation_unit_id] 820 FROM sys.allocation_units allocunits 821 INNER JOIN sys.partitions partitions ON ( allocunits.type IN ( 822 1, 3 ) 823 AND partitions.hobt_id = allocunits.container_id 824 ) 825 OR ( allocunits.type = 2 826 AND partitions.partition_id = allocunits.container_id 827 ) 828 WHERE object_id = OBJECT_ID('' + @SchemaName_n_TableName 829 + '') ) 830 AND Context IN ( 'LCX_TEXT_MIX' ) 831 AND Operation IN ( 'LOP_DELETE_ROWS' ) 832 /*Use this subquery to filter the date*/ 833 AND [TRANSACTION ID] IN ( 834 SELECT DISTINCT 835 [TRANSACTION ID] 836 FROM sys.fn_dblog(NULL, NULL) 837 WHERE Context IN ( 'LCX_NULL' ) 838 AND Operation IN ( 'LOP_BEGIN_XACT' ) 839 AND [Transaction Name] = 'DELETE' 840 AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From 841 AND 842 @Date_To ) 843 844 /****************************************/ 845 846 OPEN Page_Data_Cursor 847 848 FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid, 849 @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context 850 851 WHILE @@FETCH_STATUS = 0 852 BEGIN 853 DECLARE @hex_pageid AS VARCHAR(MAX) 854 /*Page ID contains File Number and page number It looks like 0001:00000130. 855 In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/ 856 SET @Fileid = SUBSTRING(@ConsolidatedPageID, 0, 857 CHARINDEX(':', @ConsolidatedPageID)) -- Seperate File ID from Page ID 858 859 SET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID, 860 CHARINDEX(':', 861 @ConsolidatedPageID) 862 + 1, LEN(@ConsolidatedPageID)) ---Seperate the page ID 863 SELECT @Pageid = CONVERT(INT, CAST('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 864 'varbinary(max)')) -- Convert Page ID from hex to integer 865 FROM ( SELECT CASE SUBSTRING(@hex_pageid, 1, 2) 866 WHEN '0x' THEN 3 867 ELSE 0 868 END 869 ) AS t ( pos ) 870 871 IF @Context = 'LCX_PFS' 872 BEGIN 873 DELETE @temppagedata 874 INSERT INTO @temppagedata 875 EXEC 876 ( 'DBCC PAGE(' + @DataBase_Name + ', ' 877 + @fileid + ', ' + @pageid 878 + ', 1) with tableresults,no_infomsgs;' 879 ); 880 INSERT INTO @pagedata 881 SELECT @ConsolidatedPageID , 882 @fileid , 883 @pageid , 884 @AllocUnitID , 885 [ParentObject] , 886 [Object] , 887 [Field] , 888 [Value] 889 FROM @temppagedata 890 END 891 ELSE 892 IF @Context = 'LCX_TEXT_MIX' 893 BEGIN 894 INSERT INTO @ModifiedRawData 895 SELECT @ConsolidatedPageID , 896 @fileid , 897 @pageid , 898 @Slotid , 899 @AllocUnitID , 900 NULL , 901 0 , 902 CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX, 903 11, 2)))) , 904 @LCX_TEXT_MIX , 905 @LinkID , 906 0 907 END 908 FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid, 909 @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context 910 END 911 912 CLOSE Page_Data_Cursor 913 DEALLOCATE Page_Data_Cursor 914 915 DECLARE @Newhexstring VARCHAR(MAX); 916 917 --The data is in multiple rows in the page, so we need to convert it into one row as a single hex value. 918 --This hex value is in string format 919 INSERT INTO @ModifiedRawData 920 ( [PAGE ID] , 921 [FILE IDS] , 922 [PAGE IDS] , 923 [Slot ID] , 924 [AllocUnitId] , 925 [RowLog Contents 0_var] , 926 [RowLog Length] 927 ) 928 SELECT [Page ID] , 929 [FILE IDS] , 930 [PAGE IDS] , 931 SUBSTRING([ParentObject], 932 CHARINDEX('Slot', [ParentObject]) + 4, 933 ( CHARINDEX('Offset', [ParentObject]) 934 - ( CHARINDEX('Slot', [ParentObject]) + 4 ) ) 935 - 2) AS [Slot ID] , 936 [AllocUnitId] , 937 SUBSTRING(( SELECT REPLACE(STUFF(( SELECT 938 REPLACE(SUBSTRING([Value], 939 CHARINDEX(':', 940 [Value]) + 1, 941 CHARINDEX('†', 942 [Value]) 943 - CHARINDEX(':', 944 [Value])), '†', 945 '') 946 FROM @pagedata C 947 WHERE B.[Page ID] = C.[Page ID] 948 AND SUBSTRING(B.[ParentObject], 949 CHARINDEX('Slot', 950 B.[ParentObject]) 951 + 4, 952 ( CHARINDEX('Offset', 953 B.[ParentObject]) 954 - ( CHARINDEX('Slot', 955 B.[ParentObject]) 956 + 4 ) )) = SUBSTRING(C.[ParentObject], 957 CHARINDEX('Slot', 958 C.[ParentObject]) 959 + 4, 960 ( CHARINDEX('Offset', 961 C.[ParentObject]) 962 - ( CHARINDEX('Slot', 963 C.[ParentObject]) 964 + 4 ) )) 965 AND [Object] LIKE '%Memory Dump%' 966 ORDER BY '0x' 967 + LEFT([Value], 968 CHARINDEX(':', 969 [Value]) - 1) 970 FOR 971 XML PATH('') 972 ), 1, 1, ''), ' ', '') 973 ), 1, 20000) AS [Value] , 974 SUBSTRING(( SELECT '0x' 975 + REPLACE(STUFF(( SELECT 976 REPLACE(SUBSTRING([Value], 977 CHARINDEX(':', 978 [Value]) + 1, 979 CHARINDEX('†', 980 [Value]) 981 - CHARINDEX(':', 982 [Value])), '†', 983 '') 984 FROM 985 @pagedata C 986 WHERE 987 B.[Page ID] = C.[Page ID] 988 AND SUBSTRING(B.[ParentObject], 989 CHARINDEX('Slot', 990 B.[ParentObject]) 991 + 4, 992 ( CHARINDEX('Offset', 993 B.[ParentObject]) 994 - ( CHARINDEX('Slot', 995 B.[ParentObject]) 996 + 4 ) )) = SUBSTRING(C.[ParentObject], 997 CHARINDEX('Slot', 998 C.[ParentObject]) 999 + 4,1000 ( CHARINDEX('Offset',1001 C.[ParentObject])1002 - ( CHARINDEX('Slot',1003 C.[ParentObject])1004 + 4 ) ))1005 AND [Object] LIKE '%Memory Dump%'1006 ORDER BY '0x'1007 + LEFT([Value],1008 CHARINDEX(':',1009 [Value]) - 1)1010 FOR1011 XML PATH('')1012 ), 1, 1, ''), ' ', '')1013 ), 7, 4) AS [Length]1014 FROM @pagedata B1015 WHERE [Object] LIKE '%Memory Dump%'1016 GROUP BY [Page ID] ,1017 [FILE IDS] ,1018 [PAGE IDS] ,1019 [ParentObject] ,1020 [AllocUnitId]--,[Current LSN]1021 ORDER BY [Slot ID]1022 1023 UPDATE @ModifiedRawData1024 SET [RowLog Len] = CONVERT(VARBINARY(8000), REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))',1025 'varbinary(Max)')))1026 FROM @ModifiedRawData1027 WHERE [LINK ID] = 01028 1029 UPDATE @ModifiedRawData1030 SET [RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))',1031 'varbinary(Max)')1032 FROM @ModifiedRawData1033 WHERE [LINK ID] = 01034 1035 UPDATE B1036 SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL1037 AND C.[RowLog Contents 0] IS NOT NULL1038 THEN A.[RowLog Contents 0]1039 + C.[RowLog Contents 0]1040 WHEN A.[RowLog Contents 0] IS NULL1041 AND C.[RowLog Contents 0] IS NOT NULL1042 THEN C.[RowLog Contents 0]1043 WHEN A.[RowLog Contents 0] IS NOT NULL1044 AND C.[RowLog Contents 0] IS NULL1045 THEN A.[RowLog Contents 0]1046 END ) ,1047 B.[Update] = ISNULL(B.[Update], 0) + 11048 FROM @ModifiedRawData B1049 LEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1050 15 + 14, 2))))1051 AND A.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1052 19 + 14, 2))))1053 AND A.[Link ID] = B.[Link ID]1054 LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1055 27 + 14, 2))))1056 AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1057 31 + 14, 2))))1058 AND C.[Link ID] = B.[Link ID]1059 WHERE ( A.[RowLog Contents 0] IS NOT NULL1060 OR C.[RowLog Contents 0] IS NOT NULL1061 )1062 1063 1064 UPDATE B1065 SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL1066 AND C.[RowLog Contents 0] IS NOT NULL1067 THEN A.[RowLog Contents 0]1068 + C.[RowLog Contents 0]1069 WHEN A.[RowLog Contents 0] IS NULL1070 AND C.[RowLog Contents 0] IS NOT NULL1071 THEN C.[RowLog Contents 0]1072 WHEN A.[RowLog Contents 0] IS NOT NULL1073 AND C.[RowLog Contents 0] IS NULL1074 THEN A.[RowLog Contents 0]1075 END )1076 --,B.[Update]=ISNULL(B.[Update],0)+11077 FROM @ModifiedRawData B1078 LEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1079 15 + 14, 2))))1080 AND A.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1081 19 + 14, 2))))1082 AND A.[Link ID] <> B.[Link ID]1083 AND B.[Update] = 01084 LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1085 27 + 14, 2))))1086 AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],1087 31 + 14, 2))))1088 AND C.[Link ID] <> B.[Link ID]1089 AND B.[Update] = 01090 WHERE ( A.[RowLog Contents 0] IS NOT NULL1091 OR C.[RowLog Contents 0] IS NOT NULL1092 )1093 1094 UPDATE @ModifiedRawData1095 SET [RowLog Contents 0] = ( CASE WHEN [RowLog Len] >= 80001096 THEN SUBSTRING([RowLog Contents 0],1097 15, [RowLog Len])1098 WHEN [RowLog Len] < 80001099 THEN SUBSTRING([RowLog Contents 0],1100 15 + 6,1101 CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents 0],1102 15, 6)))))1103 END )1104 FROM @ModifiedRawData1105 WHERE [LINK ID] = 01106 1107 UPDATE @ColumnNameAndData1108 SET [hex_Value] = [RowLog Contents 0] 1109 --,A.[Update]=A.[Update]+11110 FROM @ColumnNameAndData A1111 INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],1112 17, 4)))) = [PAGE IDS]1113 AND CONVERT(INT, SUBSTRING([hex_value],1114 9, 2)) = B.[Link ID]1115 WHERE [System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )1116 AND [Link ID] <> 0 1117 1118 UPDATE @ColumnNameAndData1119 SET [hex_Value] = ( CASE WHEN B.[RowLog Contents 0] IS NOT NULL1120 AND C.[RowLog Contents 0] IS NOT NULL1121 THEN B.[RowLog Contents 0]1122 + C.[RowLog Contents 0]1123 WHEN B.[RowLog Contents 0] IS NULL1124 AND C.[RowLog Contents 0] IS NOT NULL1125 THEN C.[RowLog Contents 0]1126 WHEN B.[RowLog Contents 0] IS NOT NULL1127 AND C.[RowLog Contents 0] IS NULL1128 THEN B.[RowLog Contents 0]1129 END )1130 --,A.[Update]=A.[Update]+11131 FROM @ColumnNameAndData A1132 LEFT JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],1133 5, 4)))) = B.[PAGE IDS]1134 AND B.[Link ID] = 01135 LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],1136 17, 4)))) = C.[PAGE IDS]1137 AND C.[Link ID] = 01138 WHERE [System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )1139 AND ( B.[RowLog Contents 0] IS NOT NULL1140 OR C.[RowLog Contents 0] IS NOT NULL1141 )1142 1143 UPDATE @ColumnNameAndData1144 SET [hex_Value] = [RowLog Contents 0] 1145 --,A.[Update]=A.[Update]+11146 FROM @ColumnNameAndData A1147 INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],1148 9, 4)))) = [PAGE IDS]1149 AND CONVERT(INT, SUBSTRING([hex_value],1150 3, 2)) = [Link ID]1151 WHERE [System_Type_Id] IN ( 35, 34, 99 )1152 AND [Link ID] <> 0 1153 1154 UPDATE @ColumnNameAndData1155 SET [hex_Value] = [RowLog Contents 0]1156 --,A.[Update]=A.[Update]+101157 FROM @ColumnNameAndData A1158 INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],1159 9, 4)))) = [PAGE IDS]1160 WHERE [System_Type_Id] IN ( 35, 34, 99 )1161 AND [Link ID] = 01162 1163 UPDATE @ColumnNameAndData1164 SET [hex_Value] = [RowLog Contents 0] 1165 --,A.[Update]=A.[Update]+11166 FROM @ColumnNameAndData A1167 INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],1168 15, 4)))) = [PAGE IDS]1169 WHERE [System_Type_Id] IN ( 35, 34, 99 )1170 AND [Link ID] = 01171 1172 UPDATE @ColumnNameAndData1173 SET [hex_value] = 0xFFFE + SUBSTRING([hex_value], 9, LEN([hex_value]))1174 --,[Update]=[Update]+11175 WHERE [system_type_id] = 2411176 1177 CREATE TABLE [#temp_Data]1178 (1179 [FieldName] VARCHAR(MAX) ,1180 [FieldValue] NVARCHAR(MAX) ,1181 [Rowlogcontents] VARBINARY(8000) ,1182 [Row ID] INT1183 )1184 1185 INSERT INTO #temp_Data1186 SELECT NAME ,1187 CASE WHEN system_type_id IN ( 231, 239 )1188 THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --NVARCHAR ,NCHAR1189 WHEN system_type_id IN ( 167, 175 )1190 THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --VARCHAR,CHAR1191 WHEN system_type_id IN ( 35 )1192 THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --Text1193 WHEN system_type_id IN ( 99 )1194 THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --nText 1195 WHEN system_type_id = 481196 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE(hex_Value)))) --TINY INTEGER1197 WHEN system_type_id = 521198 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGER1199 WHEN system_type_id = 561200 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER1201 WHEN system_type_id = 1271202 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER1203 WHEN system_type_id = 611204 THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --DATETIME1205 WHEN system_type_id = 581206 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIME1207 WHEN system_type_id = 1081208 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)1209 + CONVERT(VARBINARY(1), xscale))1210 + CONVERT(VARBINARY(1), 0) + hex_Value)) --- NUMERIC1211 WHEN system_type_id = 1061212 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)1213 + CONVERT(VARBINARY(1), xscale))1214 + CONVERT(VARBINARY(1), 0) + hex_Value)) --- DECIMAL1215 WHEN system_type_id IN ( 60, 122 )1216 THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEY1217 WHEN system_type_id = 1041218 THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value)1219 % 2)) -- BIT1220 WHEN system_type_id = 621221 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))1222 * ( 1.01223 + ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)1224 & 0x000FFFFFFFFFFFFF )1225 * POWER(CAST(2 AS FLOAT),1226 -52) )1227 * POWER(CAST(2 AS FLOAT),1228 ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)1229 & 0x7ff0000000000000 )1230 / EXP(52 * LOG(2))1231 - 1023 ))), 53,1232 LEN(hex_Value)))) --- FLOAT1233 WHEN system_type_id = 591234 THEN LEFT(LTRIM(STR(CAST(SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))1235 * ( 1.01236 + ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)1237 & 0x007FFFFF )1238 * POWER(CAST(2 AS REAL), -23) )1239 * POWER(CAST(2 AS REAL),1240 ( ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS INT) )1241 & 0x7f800000 )1242 / EXP(23 * LOG(2))1243 - 127 )) AS REAL), 23,1244 23)), 8) --Real1245 WHEN system_type_id IN ( 165, 173 )1246 THEN ( CASE WHEN CHARINDEX(0x,1247 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1248 'VARBINARY(8000)')) = 01249 THEN '0x'1250 ELSE ''1251 END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1252 'varchar(max)') -- BINARY,VARBINARY1253 WHEN system_type_id = 341254 THEN ( CASE WHEN CHARINDEX(0x,1255 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1256 'VARBINARY(8000)')) = 01257 THEN '0x'1258 ELSE ''1259 END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1260 'varchar(max)') --IMAGE1261 WHEN system_type_id = 361262 THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value)) --UNIQUEIDENTIFIER1263 WHEN system_type_id = 2311264 THEN CONVERT(VARCHAR(MAX), CONVERT(SYSNAME, hex_Value)) --SYSNAME1265 WHEN system_type_id = 2411266 THEN CONVERT(VARCHAR(MAX), CONVERT(XML, hex_Value)) --XML1267 WHEN system_type_id = 1891268 THEN ( CASE WHEN CHARINDEX(0x,1269 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1270 'VARBINARY(8000)')) = 01271 THEN '0x'1272 ELSE ''1273 END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1274 'varchar(max)') --TIMESTAMP1275 WHEN system_type_id = 981276 THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1277 1)) = 561278 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value,1279 3,1280 LEN(hex_Value)))))) -- INTEGER1281 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1282 1)) = 1081283 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38,1284 20), CONVERT(VARBINARY(1), SUBSTRING(hex_Value,1285 3, 1))1286 + CONVERT(VARBINARY(1), SUBSTRING(hex_Value,1287 4, 1))1288 + CONVERT(VARBINARY(1), 0)1289 + SUBSTRING(hex_Value, 5,1290 LEN(hex_Value)))) --- NUMERIC1291 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1292 1)) = 1671293 THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,1294 9,1295 LEN(hex_Value))))) --VARCHAR,CHAR1296 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1297 1)) = 361298 THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),1299 3, 20))) --UNIQUEIDENTIFIER1300 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1301 1)) = 611302 THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value,1303 3,1304 LEN(hex_Value))))), 100) --DATETIME1305 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,1306 1)) = 1651307 THEN '0x'1308 + SUBSTRING(( CASE WHEN CHARINDEX(0x,1309 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1310 'VARBINARY(8000)')) = 01311 THEN '0x'1312 ELSE ''1313 END )1314 + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',1315 'varchar(max)'),1316 11, LEN(hex_Value)) -- BINARY,VARBINARY1317 END )1318 END AS FieldValue ,1319 [Rowlogcontents] ,1320 [Row ID]1321 FROM @ColumnNameAndData1322 ORDER BY nullbit1323 1324 --Create the column name in the same order to do pivot table.1325 1326 DECLARE @FieldName VARCHAR(MAX)1327 SET @FieldName = STUFF(( SELECT ','1328 + CAST(QUOTENAME([Name]) AS VARCHAR(MAX))1329 FROM syscolumns1330 WHERE id = OBJECT_ID(''1331 + @SchemaName_n_TableName1332 + '')1333 FOR1334 XML PATH('')1335 ), 1, 1, '')1336 1337 --Finally did pivot table and get the data back in the same format.1338 1339 SET @sql = 'SELECT ' + @FieldName1340 + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN ('1341 + @FieldName + ')) AS pvt'1342 EXEC sp_executesql @sql1343 1344 GO --Create TableCreate Table [Test_Table]([Col_image] image,[Col_text] text,[Col_uniqueidentifier] uniqueidentifier,[Col_tinyint] tinyint,[Col_smallint] smallint,[Col_int] int,[Col_smalldatetime] smalldatetime,[Col_real] real,[Col_money] money,[Col_datetime] datetime,[Col_float] float,[Col_Int_sql_variant] sql_variant,[Col_numeric_sql_variant] sql_variant,[Col_varchar_sql_variant] sql_variant,[Col_uniqueidentifier_sql_variant] sql_variant,[Col_Date_sql_variant] sql_variant,[Col_varbinary_sql_variant] sql_variant,[Col_ntext] ntext,[Col_bit] bit,[Col_decimal] decimal(18,4),[Col_numeric] numeric(18,4),[Col_smallmoney] smallmoney,[Col_bigint] bigint,[Col_varbinary] varbinary(Max),[Col_varchar] varchar(Max),[Col_binary] binary(8),[Col_char] char,[Col_timestamp] timestamp,[Col_nvarchar] nvarchar(Max),[Col_nchar] nchar,[Col_xml] xml,[Col_sysname] sysname)GO--Insert data into itINSERT INTO [Test_Table] ([Col_image] ,[Col_text] ,[Col_uniqueidentifier] ,[Col_tinyint] ,[Col_smallint] ,[Col_int] ,[Col_smalldatetime] ,[Col_real] ,[Col_money] ,[Col_datetime] ,[Col_float] ,[Col_Int_sql_variant] ,[Col_numeric_sql_variant] ,[Col_varchar_sql_variant] ,[Col_uniqueidentifier_sql_variant] ,[Col_Date_sql_variant] ,[Col_varbinary_sql_variant] ,[Col_ntext] ,[Col_bit] ,[Col_decimal] ,[Col_numeric] ,[Col_smallmoney] ,[Col_bigint] ,[Col_varbinary] ,[Col_varchar] ,[Col_binary] ,[Col_char] ,[Col_nvarchar] ,[Col_nchar] ,[Col_xml] ,[Col_sysname]) VALUES (CONVERT(IMAGE,REPLICATE('A',4000)) ,REPLICATE('B',8000) ,NEWID() ,10 ,20 ,3000 ,GETDATE() ,4000 ,5000 ,getdate()+15 ,66666.6666 ,777777 ,88888.8888 ,REPLICATE('C',8000) ,newid() ,getdate()+30 ,CONVERT(VARBINARY(8000),REPLICATE('D',8000)) ,REPLICATE('E',4000) ,1 ,99999.9999 ,10101.1111 ,1100 ,123456 ,CONVERT(VARBINARY(MAX),REPLICATE('F',8000)) ,REPLICATE('G',8000) ,0x4646464 ,'H' ,REPLICATE('I',4000) ,'J' ,CONVERT(XML,REPLICATE('K',4000)) ,REPLICATE('L',100) )GO--Delete the dataDelete from Test_TableGo--Verify the dataSelect * from Test_TableGo--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'