如何根据日志查看删除的数据（转译）
原文地址：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'本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016-06-07 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。