如何根据日志查看删除的数据（转译）
原文地址：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 FOR 1011 XML PATH('') 1012 ), 1, 1, ''), ' ', '') 1013 ), 7, 4) AS [Length] 1014 FROM @pagedata B 1015 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 @ModifiedRawData 1024 SET [RowLog Len] = CONVERT(VARBINARY(8000), REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))', 1025 'varbinary(Max)'))) 1026 FROM @ModifiedRawData 1027 WHERE [LINK ID] = 0 1028 1029 UPDATE @ModifiedRawData 1030 SET [RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))', 1031 'varbinary(Max)') 1032 FROM @ModifiedRawData 1033 WHERE [LINK ID] = 0 1034 1035 UPDATE B 1036 SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL 1037 AND C.[RowLog Contents 0] IS NOT NULL 1038 THEN A.[RowLog Contents 0] 1039 + C.[RowLog Contents 0] 1040 WHEN A.[RowLog Contents 0] IS NULL 1041 AND C.[RowLog Contents 0] IS NOT NULL 1042 THEN C.[RowLog Contents 0] 1043 WHEN A.[RowLog Contents 0] IS NOT NULL 1044 AND C.[RowLog Contents 0] IS NULL 1045 THEN A.[RowLog Contents 0] 1046 END ) , 1047 B.[Update] = ISNULL(B.[Update], 0) + 1 1048 FROM @ModifiedRawData B 1049 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 NULL 1060 OR C.[RowLog Contents 0] IS NOT NULL 1061 ) 1062 1063 1064 UPDATE B 1065 SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL 1066 AND C.[RowLog Contents 0] IS NOT NULL 1067 THEN A.[RowLog Contents 0] 1068 + C.[RowLog Contents 0] 1069 WHEN A.[RowLog Contents 0] IS NULL 1070 AND C.[RowLog Contents 0] IS NOT NULL 1071 THEN C.[RowLog Contents 0] 1072 WHEN A.[RowLog Contents 0] IS NOT NULL 1073 AND C.[RowLog Contents 0] IS NULL 1074 THEN A.[RowLog Contents 0] 1075 END ) 1076 --,B.[Update]=ISNULL(B.[Update],0)+1 1077 FROM @ModifiedRawData B 1078 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] = 0 1084 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] = 0 1090 WHERE ( A.[RowLog Contents 0] IS NOT NULL 1091 OR C.[RowLog Contents 0] IS NOT NULL 1092 ) 1093 1094 UPDATE @ModifiedRawData 1095 SET [RowLog Contents 0] = ( CASE WHEN [RowLog Len] >= 8000 1096 THEN SUBSTRING([RowLog Contents 0], 1097 15, [RowLog Len]) 1098 WHEN [RowLog Len] < 8000 1099 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 @ModifiedRawData 1105 WHERE [LINK ID] = 0 1106 1107 UPDATE @ColumnNameAndData 1108 SET [hex_Value] = [RowLog Contents 0] 1109 --,A.[Update]=A.[Update]+1 1110 FROM @ColumnNameAndData A 1111 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 @ColumnNameAndData 1119 SET [hex_Value] = ( CASE WHEN B.[RowLog Contents 0] IS NOT NULL 1120 AND C.[RowLog Contents 0] IS NOT NULL 1121 THEN B.[RowLog Contents 0] 1122 + C.[RowLog Contents 0] 1123 WHEN B.[RowLog Contents 0] IS NULL 1124 AND C.[RowLog Contents 0] IS NOT NULL 1125 THEN C.[RowLog Contents 0] 1126 WHEN B.[RowLog Contents 0] IS NOT NULL 1127 AND C.[RowLog Contents 0] IS NULL 1128 THEN B.[RowLog Contents 0] 1129 END ) 1130 --,A.[Update]=A.[Update]+1 1131 FROM @ColumnNameAndData A 1132 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] = 0 1135 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] = 0 1138 WHERE [System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 ) 1139 AND ( B.[RowLog Contents 0] IS NOT NULL 1140 OR C.[RowLog Contents 0] IS NOT NULL 1141 ) 1142 1143 UPDATE @ColumnNameAndData 1144 SET [hex_Value] = [RowLog Contents 0] 1145 --,A.[Update]=A.[Update]+1 1146 FROM @ColumnNameAndData A 1147 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 @ColumnNameAndData 1155 SET [hex_Value] = [RowLog Contents 0] 1156 --,A.[Update]=A.[Update]+10 1157 FROM @ColumnNameAndData A 1158 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] = 0 1162 1163 UPDATE @ColumnNameAndData 1164 SET [hex_Value] = [RowLog Contents 0] 1165 --,A.[Update]=A.[Update]+1 1166 FROM @ColumnNameAndData A 1167 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] = 0 1171 1172 UPDATE @ColumnNameAndData 1173 SET [hex_value] = 0xFFFE + SUBSTRING([hex_value], 9, LEN([hex_value])) 1174 --,[Update]=[Update]+1 1175 WHERE [system_type_id] = 241 1176 1177 CREATE TABLE [#temp_Data] 1178 ( 1179 [FieldName] VARCHAR(MAX) , 1180 [FieldValue] NVARCHAR(MAX) , 1181 [Rowlogcontents] VARBINARY(8000) , 1182 [Row ID] INT 1183 ) 1184 1185 INSERT INTO #temp_Data 1186 SELECT NAME , 1187 CASE WHEN system_type_id IN ( 231, 239 ) 1188 THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --NVARCHAR ,NCHAR 1189 WHEN system_type_id IN ( 167, 175 ) 1190 THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --VARCHAR,CHAR 1191 WHEN system_type_id IN ( 35 ) 1192 THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --Text 1193 WHEN system_type_id IN ( 99 ) 1194 THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --nText 1195 WHEN system_type_id = 48 1196 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE(hex_Value)))) --TINY INTEGER 1197 WHEN system_type_id = 52 1198 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGER 1199 WHEN system_type_id = 56 1200 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER 1201 WHEN system_type_id = 127 1202 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER 1203 WHEN system_type_id = 61 1204 THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --DATETIME 1205 WHEN system_type_id = 58 1206 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIME 1207 WHEN system_type_id = 108 1208 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)) --- NUMERIC 1211 WHEN system_type_id = 106 1212 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)) --- DECIMAL 1215 WHEN system_type_id IN ( 60, 122 ) 1216 THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEY 1217 WHEN system_type_id = 104 1218 THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value) 1219 % 2)) -- BIT 1220 WHEN system_type_id = 62 1221 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)) 1222 * ( 1.0 1223 + ( 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)))) --- FLOAT 1233 WHEN system_type_id = 59 1234 THEN LEFT(LTRIM(STR(CAST(SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)) 1235 * ( 1.0 1236 + ( 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) --Real 1245 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)')) = 0 1249 THEN '0x' 1250 ELSE '' 1251 END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 1252 'varchar(max)') -- BINARY,VARBINARY 1253 WHEN system_type_id = 34 1254 THEN ( CASE WHEN CHARINDEX(0x, 1255 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 1256 'VARBINARY(8000)')) = 0 1257 THEN '0x' 1258 ELSE '' 1259 END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 1260 'varchar(max)') --IMAGE 1261 WHEN system_type_id = 36 1262 THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value)) --UNIQUEIDENTIFIER 1263 WHEN system_type_id = 231 1264 THEN CONVERT(VARCHAR(MAX), CONVERT(SYSNAME, hex_Value)) --SYSNAME 1265 WHEN system_type_id = 241 1266 THEN CONVERT(VARCHAR(MAX), CONVERT(XML, hex_Value)) --XML 1267 WHEN system_type_id = 189 1268 THEN ( CASE WHEN CHARINDEX(0x, 1269 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 1270 'VARBINARY(8000)')) = 0 1271 THEN '0x' 1272 ELSE '' 1273 END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 1274 'varchar(max)') --TIMESTAMP 1275 WHEN system_type_id = 98 1276 THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1, 1277 1)) = 56 1278 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value, 1279 3, 1280 LEN(hex_Value)))))) -- INTEGER 1281 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1, 1282 1)) = 108 1283 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)))) --- NUMERIC 1291 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1, 1292 1)) = 167 1293 THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value, 1294 9, 1295 LEN(hex_Value))))) --VARCHAR,CHAR 1296 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1, 1297 1)) = 36 1298 THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ), 1299 3, 20))) --UNIQUEIDENTIFIER 1300 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1, 1301 1)) = 61 1302 THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value, 1303 3, 1304 LEN(hex_Value))))), 100) --DATETIME 1305 WHEN CONVERT(INT, SUBSTRING(hex_Value, 1, 1306 1)) = 165 1307 THEN '0x' 1308 + SUBSTRING(( CASE WHEN CHARINDEX(0x, 1309 CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 1310 'VARBINARY(8000)')) = 0 1311 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,VARBINARY 1317 END ) 1318 END AS FieldValue , 1319 [Rowlogcontents] , 1320 [Row ID] 1321 FROM @ColumnNameAndData 1322 ORDER BY nullbit 1323 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 syscolumns 1330 WHERE id = OBJECT_ID('' 1331 + @SchemaName_n_TableName 1332 + '') 1333 FOR 1334 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 ' + @FieldName 1340 + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' 1341 + @FieldName + ')) AS pvt' 1342 EXEC sp_executesql @sql 1343 1344 GO
下面是测试数据，然后直接执行存储过程即可。
--Create Table
Create 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 it
INSERT 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 data
Delete from Test_Table
Go
--Verify the data
Select * from Test_Table
Go
--Recover the deleted data without date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'
GO
--Recover the deleted data it with date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table','2012-06-01','2012-06-30'
