[SQL][Script]救回被誤刪的資料
在 SQL Server 下有時會發現資料不知道被誰刪除的，如果我們可以事先知道有刪除的動作，那麼透過 Audit 、SQL Trace 或者是 Trigger 等機制，那麼都還有辦法做紀錄。但如果事前沒有做這些處理，那麼該怎麼來處理呢 ? 前一陣子遇到類似的狀況，但客戶雖然用 ApexSQL Log 找回被刪除的資料，但對一值想不出來這個要怎麼做。昨天跟亂馬客和 Terry 討論之後，感覺上透過 :fn_dblog 可以讀取 Log，雖然我們可以透過這個系統函數，可以把異動的動作 ( Action )和時間找出來，但資料呢 ? 看起來似乎要從紀錄中的 Binary 資料轉出來，但這方便似乎沒有相關資料說明。
透過亂馬客的協助和修正，他更改了一個版本的查看修改資料的命令。目前這個版本必須要在 Recovery Mode 是 Full 或者是 Bulk Insert 下來使用，測試起來如果是下 SQL 指令刪除資料的話 ( DELETE )，那麼透過這個 Stored Procedure，可以將被刪除的資料給找出來；但如果您的資料庫的 Recovery Mode 是 Simple 的狀況下，在還沒有發生 Checkpoint 之前，都還可以找到資料，但倘若發生 Checkpoint 的話，那麼就沒有機會了。
整個 Stored Procedure 有點複雜，中間有一大段都是 Binary 在轉碼處理，原作者的這一段有點小缺陷，並沒有辦法支援區分大小寫的資料庫，有些拼字要調整；另外針對有些時候，我們可能並不是直接下 SQL 指令刪除，可能會針對 ADO 的 Result Set 去操作，那麼他可能會把 SQL 指令變成 sp_executesql 'DELETE FROM …' 的指令，亂馬克也協助修正了這個問題，有興趣的朋友可以參考以下的程式碼。
1:
2: -- http://raresql.com/2012/10/24/sql-server-how-to-find-who-deleted-what-records-at-what-time/
3: -- Script Name: Recover_Deleted_Data_With_UID_Date_Time_Proc
4: -- Script Type : Recovery Procedure
5: -- Develop By: Muhammad Imran
6: -- Date Created: 24 Oct 2012
7: -- Modify Date:
8: -- Version : 1.2
9: -- Notes :
10: -- 1.修改不傳入DB Name改取自DB_NAME()
11: -- 2.修改 sysname DataType to nvarchar(128)
12: -- 3.使用SUSER_SNAME 代替 sysusers join 找出操作人員
13: -- 4.修正在區分大小寫的DB中，會發生的錯誤
14: -- 5.加入判斷，如下是透過SSMS的Edit Row時，刪除的[Transaction Name] 會用 user_transaction [Transaction Name] IN ('DELETE', 'user_transaction')
15: -- Example: EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc 'dbo.tbl_Sample'
16: ALTER PROCEDURE Recover_Deleted_Data_With_UID_Date_Time_Proc
17: @SchemaName_n_TableName NVARCHAR(MAX) ,
18: @Date_From DATETIME = '1900/01/01' ,
19: @Date_To DATETIME = '9999/12/31'
20: AS
21: DECLARE @RowLogContents VARBINARY(8000)
22: DECLARE @TransactionID NVARCHAR(MAX)
23: DECLARE @AllocUnitID BIGINT
24: DECLARE @AllocUnitName NVARCHAR(MAX)
25: DECLARE @SQL NVARCHAR(MAX)
26: DECLARE @Compatibility_Level INT
27: DECLARE @Database_Name NVARCHAR(MAX )
28:
29: SET @Database_Name = DB_NAME()
30: SELECT @Compatibility_Level = dtb.compatibility_level
31: FROM master.sys.databases AS dtb
32: WHERE dtb.name = @Database_Name
33:
34: IF ISNULL(@Compatibility_Level, 0) <= 80
35: BEGIN
36: RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
37: RETURN
38: END
39:
40: IF ( SELECT COUNT(*)
41: FROM INFORMATION_SCHEMA.TABLES
42: WHERE [TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName
43: ) = 0
44: BEGIN
45: RAISERROR('Could not found the table in the defined database',16,1)
46: RETURN
47: END
48:
49: DECLARE @bitTable TABLE
50: (
51: [ID] INT ,
52: [Bitvalue] INT
53: )
54: --Create table to set the bit position of one byte.
55:
56: INSERT INTO @bitTable
57: SELECT 0 ,
58: 2
59: UNION ALL
60: SELECT 1 ,
61: 2
62: UNION ALL
63: SELECT 2 ,
64: 4
65: UNION ALL
66: SELECT 3 ,
67: 8
68: UNION ALL
69: SELECT 4 ,
70: 16
71: UNION ALL
72: SELECT 5 ,
73: 32
74: UNION ALL
75: SELECT 6 ,
76: 64
77: UNION ALL
78: SELECT 7 ,
79: 128
80:
81: --Create table to collect the row data.
82: DECLARE @DeletedRecords TABLE
83: (
84: [Row ID] INT IDENTITY(1, 1) ,
85: [RowLogContents] VARBINARY(8000) ,
86: [AllocUnitId] BIGINT ,
87: [Transaction ID] NVARCHAR(MAX) ,
88: [FixedLengthData] SMALLINT ,
89: [TotalNoOfCols] SMALLINT ,
90: [NullBitMapLength] SMALLINT ,
91: [NullBytes] VARBINARY(8000) ,
92: [TotalNoofVarCols] SMALLINT ,
93: [ColumnOffsetArray] VARBINARY(8000) ,
94: [VarColumnStart] SMALLINT ,
95: [Slot ID] INT ,
96: [NullBitMap] VARCHAR(MAX)
97: )
98: --Create a common table expression to get all the row data plus how many bytes we have for each row.
99: ;
100: WITH RowData
101: AS ( SELECT [RowLog Contents 0] AS [RowLogContents] ,
102: [AllocUnitID] AS [AllocUnitID] ,
103: [Transaction ID] AS [Transaction ID]
104:
105: --[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
106: ,
107: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
108: 2 + 1, 2)))) AS [FixedLengthData] --@FixedLengthData
109:
110: -- [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
111: ,
112: CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
113: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
114: 2 + 1, 2)))) + 1,
115: 2)))) AS [TotalNoOfCols]
116:
117: --[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
118: ,
119: CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
120: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
121: 2 + 1, 2)))) + 1,
122: 2)))) / 8.0)) AS [NullBitMapLength]
123:
124: --[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
125: ,
126: SUBSTRING([RowLog Contents 0],
127: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
128: 2 + 1, 2)))) + 3,
129: CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
130: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
131: 2 + 1, 2)))) + 1,
132: 2)))) / 8.0))) AS [NullBytes]
133:
134: --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
135: ,
136: ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (
137: 0x10, 0x30, 0x70 )
138: THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
139: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
140: 2 + 1, 2)))) + 3
141: + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
142: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
143: 2 + 1, 2)))) + 1,
144: 2)))) / 8.0)), 2))))
145: ELSE NULL
146: END ) AS [TotalNoofVarCols]
147:
148: --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
149: ,
150: ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (
151: 0x10, 0x30, 0x70 )
152: THEN SUBSTRING([RowLog Contents 0],
153: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
154: 2 + 1, 2)))) + 3
155: + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
156: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
157: 2 + 1, 2)))) + 1,
158: 2)))) / 8.0))
159: + 2,
160: ( CASE WHEN SUBSTRING([RowLog Contents 0],
161: 1, 1) IN ( 0x10,
162: 0x30, 0x70 )
163: THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
164: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
165: 2 + 1, 2)))) + 3
166: + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
167: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
168: 2 + 1, 2)))) + 1,
169: 2)))) / 8.0)), 2))))
170: ELSE NULL
171: END ) * 2)
172: ELSE NULL
173: END ) AS [ColumnOffsetArray]
174:
175: -- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
176: ,
177: CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (
178: 0x10, 0x30, 0x70 )
179: THEN ( CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
180: 2 + 1, 2)))) + 4
181: + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
182: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
183: 2 + 1, 2)))) + 1,
184: 2)))) / 8.0))
185: + ( ( CASE WHEN SUBSTRING([RowLog Contents 0],
186: 1, 1) IN ( 0x10,
187: 0x30, 0x70 )
188: THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
189: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
190: 2 + 1, 2)))) + 3
191: + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
192: CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
193: 2 + 1, 2)))) + 1,
194: 2)))) / 8.0)), 2))))
195: ELSE NULL
196: END ) * 2 ) )
197: ELSE NULL
198: END AS [VarColumnStart] ,
199: [Slot ID]
200: FROM sys.fn_dblog(NULL, NULL)
201: WHERE AllocUnitId IN (
202: SELECT [Allocation_unit_id]
203: FROM sys.allocation_units allocunits
204: INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
205: 1, 3 )
206: AND partitions.hobt_id = allocunits.container_id
207: )
208: OR ( allocunits.type = 2
209: AND partitions.partition_id = allocunits.container_id
210: )
211: WHERE object_id = OBJECT_ID(''
212: + @SchemaName_n_TableName
213: + '') )
214: AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )
215: AND Operation IN ( 'LOP_DELETE_ROWS' )
216: AND SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x10,
217: 0x30, 0x70 )
218:
219: /*Use this subquery to filter the date*/
220: AND [TRANSACTION ID] IN (
221: SELECT DISTINCT
222: [TRANSACTION ID]
223: FROM sys.fn_dblog(NULL, NULL)
224: WHERE Context IN ( 'LCX_NULL' )
225: AND Operation IN ( 'LOP_BEGIN_XACT' )
226: AND [Transaction Name] IN ('DELETE', 'user_transaction')
227: AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
228: AND
229: @Date_To)
230: ),
231:
232: --Use this technique to repeate the row till the no of bytes of the row.
233: N1 ( n )
234: AS ( SELECT 1
235: UNION ALL
236: SELECT 1
237: ),
238: N2 ( n )
239: AS ( SELECT 1
240: FROM N1 AS X ,
241: N1 AS Y
242: ),
243: N3 ( n )
244: AS ( SELECT 1
245: FROM N2 AS X ,
246: N2 AS Y
247: ),
248: N4 ( n )
249: AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n )
250: FROM N3 AS X ,
251: N3 AS Y
252: )
253: INSERT INTO @DeletedRecords
254: SELECT RowLogContents ,
255: [AllocUnitID] ,
256: [Transaction ID] ,
257: [FixedLengthData] ,
258: [TotalNoOfCols] ,
259: [NullBitMapLength] ,
260: [NullBytes] ,
261: [TotalNoofVarCols] ,
262: [ColumnOffsetArray] ,
263: [VarColumnStart] ,
264: [Slot ID]
265: ---Get the Null value against each column (1 means null zero means not null)
266: ,
267: [NullBitMap] = ( REPLACE(STUFF(( SELECT
268: ','
269: + ( CASE
270: WHEN [ID] = 0
271: THEN CONVERT(NVARCHAR(1), ( SUBSTRING(NullBytes,
272: n, 1) % 2 ))
273: ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(NullBytes,
274: n, 1)
275: / [Bitvalue] )
276: % 2 ))
277: END ) --as [NullBitMap]
278: FROM N4 AS Nums
279: JOIN RowData AS C ON n <= NullBitMapLength
280: CROSS JOIN @bitTable
281: WHERE
282: C.[RowLogContents] = D.[RowLogContents]
283: ORDER BY [RowLogContents] ,
284: n ASC
285: FOR
286: XML PATH('')
287: ), 1, 1, ''), ',', '') )
288: FROM RowData D
289:
290: IF ( SELECT COUNT(*)
291: FROM @DeletedRecords
292: ) = 0
293: BEGIN
294: RAISERROR('There is no data in the log as per the search criteria',16,1)
295: RETURN
296: END
297:
298: DECLARE @ColumnNameAndData TABLE
299: (
300: [Transaction ID] VARCHAR(100) ,
301: [Row ID] INT ,
302: [RowLogContents] VARBINARY(MAX) ,
303: [NAME] nvarchar(128) ,
304: [nullbit] SMALLINT ,
305: [leaf_offset] SMALLINT ,
306: [length] SMALLINT ,
307: [system_type_id] TINYINT ,
308: [bitpos] TINYINT ,
309: [xprec] TINYINT ,
310: [xscale] TINYINT ,
311: [is_null] INT ,
312: [Column value Size] INT ,
313: [Column Length] INT ,
314: [hex_Value] VARBINARY(MAX) ,
315: [Slot ID] INT ,
316: [Update] INT
317: )
318:
319: --Create common table expression and join it with the rowdata table
320: -- to get each column details
321: /*This part is for variable data columns*/
322: --@RowLogContents,
323: --(col.columnOffValue - col.columnLength) + 1,
324: --col.columnLength
325: --)
326: INSERT INTO @ColumnNameAndData
327: SELECT [Transaction ID] ,
328: [Row ID] ,
329: RowLogContents ,
330: NAME ,
331: cols.leaf_null_bit AS nullbit ,
332: leaf_offset ,
333: ISNULL(syscolumns.length, cols.max_length) AS [length] ,
334: cols.system_type_id ,
335: cols.leaf_bit_position AS bitpos ,
336: ISNULL(syscolumns.xprec, cols.precision) AS xprec ,
337: ISNULL(syscolumns.xscale, cols.scale) AS xscale ,
338: SUBSTRING([NullBitMap], cols.leaf_null_bit, 1) AS is_null ,
339: ( CASE WHEN leaf_offset < 1
340: AND SUBSTRING([NullBitMap], cols.leaf_null_bit,
341: 1) = 0
342: THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
343: ( 2
344: * leaf_offset
345: * -1 ) - 1, 2)))) > 30000
346: THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
347: ( 2
348: * leaf_offset
349: * -1 ) - 1, 2))))
350: - POWER(2, 15)
351: ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
352: ( 2
353: * leaf_offset
354: * -1 ) - 1, 2))))
355: END )
356: END ) AS [Column value Size] ,
357: ( CASE WHEN leaf_offset < 1
358: AND SUBSTRING([NullBitMap], cols.leaf_null_bit,
359: 1) = 0
360: THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
361: ( 2
362: * leaf_offset
363: * -1 ) - 1, 2)))) > 30000
364: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
365: ( 2
366: * ( ( leaf_offset
367: * -1 ) - 1 ) )
368: - 1, 2)))), 0),
369: [VarColumnStart]) < 30000
370: THEN ( CASE WHEN [system_type_id] IN (
371: 35, 34, 99 ) THEN 16
372: ELSE 24
373: END )
374: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
375: ( 2
376: * leaf_offset
377: * -1 ) - 1, 2)))) > 30000
378: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
379: ( 2
380: * ( ( leaf_offset
381: * -1 ) - 1 ) )
382: - 1, 2)))), 0),
383: [VarColumnStart]) > 30000
384: THEN ( CASE WHEN [system_type_id] IN (
385: 35, 34, 99 ) THEN 16
386: ELSE 24
387: END ) --24
388: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
389: ( 2
390: * leaf_offset
391: * -1 ) - 1, 2)))) < 30000
392: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
393: ( 2
394: * ( ( leaf_offset
395: * -1 ) - 1 ) )
396: - 1, 2)))), 0),
397: [VarColumnStart]) < 30000
398: THEN ( CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
399: ( 2
400: * leaf_offset
401: * -1 ) - 1, 2))))
402: - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
403: ( 2
404: * ( ( leaf_offset
405: * -1 ) - 1 ) )
406: - 1, 2)))), 0),
407: [VarColumnStart]) )
408: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
409: ( 2
410: * leaf_offset
411: * -1 ) - 1, 2)))) < 30000
412: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
413: ( 2
414: * ( ( leaf_offset
415: * -1 ) - 1 ) )
416: - 1, 2)))), 0),
417: [VarColumnStart]) > 30000
418: THEN POWER(2, 15)
419: + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
420: ( 2
421: * leaf_offset
422: * -1 ) - 1, 2))))
423: - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
424: ( 2
425: * ( ( leaf_offset
426: * -1 ) - 1 ) )
427: - 1, 2)))), 0),
428: [VarColumnStart])
429: END )
430: END ) AS [Column Length] ,
431: ( CASE WHEN SUBSTRING([NullBitMap], cols.leaf_null_bit, 1) = 1
432: THEN NULL
433: ELSE SUBSTRING(RowLogContents,
434: ( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
435: ( 2
436: * leaf_offset
437: * -1 ) - 1, 2)))) > 30000
438: THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
439: ( 2
440: * leaf_offset
441: * -1 ) - 1, 2))))
442: - POWER(2, 15)
443: ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
444: ( 2
445: * leaf_offset
446: * -1 ) - 1, 2))))
447: END )
448: - ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
449: ( 2
450: * leaf_offset
451: * -1 ) - 1, 2)))) > 30000
452: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
453: ( 2
454: * ( ( leaf_offset
455: * -1 ) - 1 ) )
456: - 1, 2)))), 0),
457: [VarColumnStart]) < 30000
458: THEN ( CASE
459: WHEN [system_type_id] IN (
460: 35, 34, 99 )
461: THEN 16
462: ELSE 24
463: END ) --24
464: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
465: ( 2
466: * leaf_offset
467: * -1 ) - 1, 2)))) > 30000
468: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
469: ( 2
470: * ( ( leaf_offset
471: * -1 ) - 1 ) )
472: - 1, 2)))), 0),
473: [VarColumnStart]) > 30000
474: THEN ( CASE
475: WHEN [system_type_id] IN (
476: 35, 34, 99 )
477: THEN 16
478: ELSE 24
479: END ) --24
480: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
481: ( 2
482: * leaf_offset
483: * -1 ) - 1, 2)))) < 30000
484: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
485: ( 2
486: * ( ( leaf_offset
487: * -1 ) - 1 ) )
488: - 1, 2)))), 0),
489: [VarColumnStart]) < 30000
490: THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
491: ( 2
492: * leaf_offset
493: * -1 ) - 1, 2))))
494: - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
495: ( 2
496: * ( ( leaf_offset
497: * -1 ) - 1 ) )
498: - 1, 2)))), 0),
499: [VarColumnStart])
500: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
501: ( 2
502: * leaf_offset
503: * -1 ) - 1, 2)))) < 30000
504: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
505: ( 2
506: * ( ( leaf_offset
507: * -1 ) - 1 ) )
508: - 1, 2)))), 0),
509: [VarColumnStart]) > 30000
510: THEN POWER(2, 15)
511: + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
512: ( 2
513: * leaf_offset
514: * -1 ) - 1, 2))))
515: - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
516: ( 2
517: * ( ( leaf_offset
518: * -1 ) - 1 ) )
519: - 1, 2)))), 0),
520: [VarColumnStart])
521: END ) ) + 1,
522: ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
523: ( 2
524: * leaf_offset
525: * -1 ) - 1, 2)))) > 30000
526: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
527: ( 2
528: * ( ( leaf_offset
529: * -1 ) - 1 ) )
530: - 1, 2)))), 0),
531: [VarColumnStart]) < 30000
532: THEN ( CASE WHEN [system_type_id] IN (
533: 35, 34, 99 )
534: THEN 16
535: ELSE 24
536: END ) --24
537: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
538: ( 2
539: * leaf_offset
540: * -1 ) - 1, 2)))) > 30000
541: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
542: ( 2
543: * ( ( leaf_offset
544: * -1 ) - 1 ) )
545: - 1, 2)))), 0),
546: [VarColumnStart]) > 30000
547: THEN ( CASE WHEN [system_type_id] IN (
548: 35, 34, 99 )
549: THEN 16
550: ELSE 24
551: END ) --24
552: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
553: ( 2
554: * leaf_offset
555: * -1 ) - 1, 2)))) < 30000
556: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
557: ( 2
558: * ( ( leaf_offset
559: * -1 ) - 1 ) )
560: - 1, 2)))), 0),
561: [VarColumnStart]) < 30000
562: THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
563: ( 2
564: * leaf_offset
565: * -1 ) - 1, 2))))
566: - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
567: ( 2
568: * ( ( leaf_offset
569: * -1 ) - 1 ) )
570: - 1, 2)))), 0),
571: [VarColumnStart]))
572: WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
573: ( 2
574: * leaf_offset
575: * -1 ) - 1, 2)))) < 30000
576: AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
577: ( 2
578: * ( ( leaf_offset
579: * -1 ) - 1 ) )
580: - 1, 2)))), 0),
581: [VarColumnStart]) > 30000
582: THEN POWER(2, 15)
583: + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
584: ( 2
585: * leaf_offset
586: * -1 ) - 1, 2))))
587: - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
588: ( 2
589: * ( ( leaf_offset
590: * -1 ) - 1 ) )
591: - 1, 2)))), 0),
592: [VarColumnStart])
593: END ))
594: END ) AS hex_Value ,
595: [Slot ID] ,
596: 0
597: FROM @DeletedRecords A
598: INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
599: INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
600: 1, 3 )
601: AND partitions.hobt_id = allocunits.container_id
602: )
603: OR ( allocunits.type = 2
604: AND partitions.partition_id = allocunits.container_id
605: )
606: INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
607: LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id
608: AND syscolumns.colid = cols.partition_column_id
609: WHERE leaf_offset < 0
610: UNION
611: /*This part is for fixed data columns*/
612: SELECT [Transaction ID] ,
613: [Row ID] ,
614: RowLogContents ,
615: NAME ,
616: cols.leaf_null_bit AS nullbit ,
617: leaf_offset ,
618: ISNULL(syscolumns.length, cols.max_length) AS [length] ,
619: cols.system_type_id ,
620: cols.leaf_bit_position AS bitpos ,
621: ISNULL(syscolumns.xprec, cols.precision) AS xprec ,
622: ISNULL(syscolumns.xscale, cols.scale) AS xscale ,
623: SUBSTRING([NullBitMap], cols.leaf_null_bit, 1) AS is_null ,
624: ( SELECT TOP 1
625: ISNULL(SUM(CASE WHEN C.leaf_offset > 1
626: THEN max_length
627: ELSE 0
628: END), 0)
629: FROM sys.system_internals_partition_columns C
630: WHERE cols.partition_id = C.partition_id
631: AND C.leaf_null_bit < cols.leaf_null_bit
632: ) + 5 AS [Column value Size] ,
633: syscolumns.length AS [Column Length] ,
634: CASE WHEN SUBSTRING([NullBitMap], cols.leaf_null_bit, 1) = 1
635: THEN NULL
636: ELSE SUBSTRING(RowLogContents,
637: ( SELECT TOP 1
638: ISNULL(SUM(CASE
639: WHEN C.leaf_offset > 1
640: AND C.leaf_bit_position = 0
641: THEN max_length
642: ELSE 0
643: END), 0)
644: FROM sys.system_internals_partition_columns C
645: WHERE cols.partition_id = C.partition_id
646: AND C.leaf_null_bit < cols.leaf_null_bit
647: ) + 5, syscolumns.length)
648: END AS hex_Value ,
649: [Slot ID] ,
650: 0
651: FROM @DeletedRecords A
652: INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
653: INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
654: 1, 3 )
655: AND partitions.hobt_id = allocunits.container_id
656: )
657: OR ( allocunits.type = 2
658: AND partitions.partition_id = allocunits.container_id
659: )
660: INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
661: LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id
662: AND syscolumns.colid = cols.partition_column_id
663: WHERE leaf_offset > 0
664: ORDER BY nullbit
665:
666: DECLARE @BitColumnByte AS INT
667: SELECT @BitColumnByte = CONVERT(INT, CEILING(COUNT(*) / 8.0))
668: FROM @ColumnNameAndData
669: WHERE [system_type_id] = 104;
670: WITH N1 ( n )
671: AS ( SELECT 1
672: UNION ALL
673: SELECT 1
674: ),
675: N2 ( n )
676: AS ( SELECT 1
677: FROM N1 AS X ,
678: N1 AS Y
679: ),
680: N3 ( n )
681: AS ( SELECT 1
682: FROM N2 AS X ,
683: N2 AS Y
684: ),
685: N4 ( n )
686: AS ( SELECT ROW_NUMBER() OVER ( ORDER BY X.n )
687: FROM N3 AS X ,
688: N3 AS Y
689: ),
690: CTE
691: AS ( SELECT RowLogContents ,
692: [nullbit] ,
693: [BitMap] = CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT
694: ','
695: + ( CASE
696: WHEN [ID] = 0
697: THEN CONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value,
698: n, 1) % 2 ))
699: ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value,
700: n, 1)
701: / [Bitvalue] )
702: % 2 ))
703: END ) --as [NullBitMap]
704: FROM
705: N4 AS Nums
706: JOIN @ColumnNameAndData
707: AS C ON n <= @BitColumnByte
708: AND [system_type_id] = 104
709: AND bitpos = 0
710: CROSS JOIN @bitTable
711: WHERE
712: C.[RowLogContents] = D.[RowLogContents]
713: ORDER BY [RowLogContents] ,
714: n ASC
715: FOR
716: XML
717: PATH('')
718: ), 1, 1, ''),
719: ',', '') ),
720: bitpos + 1, 1)))
721: FROM @ColumnNameAndData D
722: WHERE [system_type_id] = 104
723: )
724: UPDATE A
725: SET [hex_Value] = [BitMap]
726: FROM @ColumnNameAndData A
727: INNER JOIN CTE B ON A.[RowLogContents] = B.[RowLogContents]
728: AND A.[nullbit] = B.[nullbit]
729:
730:
731: /**************Check for BLOB DATA TYPES******************************/
732: DECLARE @Fileid INT
733: DECLARE @Pageid INT
734: DECLARE @Slotid INT
735: DECLARE @CurrentLSN INT
736: DECLARE @LinkID INT
737: DECLARE @Context VARCHAR(50)
738: DECLARE @ConsolidatedPageID VARCHAR(MAX)
739: DECLARE @LCX_TEXT_MIX VARBINARY(MAX)
740:
741: DECLARE @temppagedata TABLE
742: (
743: [ParentObject] nvarchar(128) ,
744: [Object] nvarchar(128) ,
745: [Field] nvarchar(128) ,
746: [Value] nvarchar(128)
747: )
748:
749: DECLARE @pagedata TABLE
750: (
751: [Page ID] nvarchar(128) ,
752: [FILE IDS] INT ,
753: [PAGE IDS] INT ,
754: [AllocUnitId] BIGINT ,
755: [ParentObject] nvarchar(128) ,
756: [Object] nvarchar(128) ,
757: [Field] nvarchar(128) ,
758: [Value] nvarchar(128)
759: )
760:
761: DECLARE @ModifiedRawData TABLE
762: (
763: [ID] INT IDENTITY(1, 1) ,
764: [PAGE ID] VARCHAR(MAX) ,
765: [FILE IDS] INT ,
766: [PAGE IDS] INT ,
767: [Slot ID] INT ,
768: [AllocUnitId] BIGINT ,
769: [RowLog Contents 0_var] VARCHAR(MAX) ,
770: [RowLog Length] VARCHAR(50) ,
771: [RowLog Len] INT ,
772: [RowLog Contents 0] VARBINARY(MAX) ,
773: [LINK ID] INT DEFAULT ( 0 ) ,
774: [Update] INT
775: )
776:
777: DECLARE Page_Data_Cursor CURSOR
778: FOR
779: /*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*/
780: SELECT LTRIM(RTRIM(REPLACE([Description], 'Deallocated', ''))) AS [PAGE ID] ,
781: [Slot ID] ,
782: [AllocUnitId] ,
783: NULL AS [RowLog Contents 0] ,
784: NULL AS [RowLog Contents 0] ,
785: Context
786: FROM sys.fn_dblog(NULL, NULL)
787: WHERE AllocUnitId IN (
788: SELECT [Allocation_unit_id]
789: FROM sys.allocation_units allocunits
790: INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
791: 1, 3 )
792: AND partitions.hobt_id = allocunits.container_id
793: )
794: OR ( allocunits.type = 2
795: AND partitions.partition_id = allocunits.container_id
796: )
797: WHERE object_id = OBJECT_ID('' + @SchemaName_n_TableName
798: + '') )
799: AND Operation IN ( 'LOP_MODIFY_ROW' )
800: AND [Context] IN ( 'LCX_PFS' )
801: AND Description LIKE '%Deallocated%'
802: /*Use this subquery to filter the date*/
803: AND [TRANSACTION ID] IN (
804: SELECT DISTINCT
805: [TRANSACTION ID]
806: FROM sys.fn_dblog(NULL, NULL)
807: WHERE Context IN ( 'LCX_NULL' )
808: AND Operation IN ( 'LOP_BEGIN_XACT' )
809: AND [Transaction Name] IN ('DELETE', 'user_transaction')
810: AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
811: AND
812: @Date_To )
813: GROUP BY [Description] ,
814: [Slot ID] ,
815: [AllocUnitId] ,
816: Context
817: UNION
818: SELECT [PAGE ID] ,
819: [Slot ID] ,
820: [AllocUnitId] ,
821: SUBSTRING([RowLog Contents 0], 15,
822: LEN([RowLog Contents 0])) AS [RowLog Contents 0] ,
823: CONVERT(INT, SUBSTRING([RowLog Contents 0], 7, 2)) ,
824: Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
825: FROM sys.fn_dblog(NULL, NULL)
826: WHERE AllocUnitId IN (
827: SELECT [Allocation_unit_id]
828: FROM sys.allocation_units allocunits
829: INNER JOIN sys.partitions partitions ON ( allocunits.type IN (
830: 1, 3 )
831: AND partitions.hobt_id = allocunits.container_id
832: )
833: OR ( allocunits.type = 2
834: AND partitions.partition_id = allocunits.container_id
835: )
836: WHERE object_id = OBJECT_ID('' + @SchemaName_n_TableName
837: + '') )
838: AND Context IN ( 'LCX_TEXT_MIX' )
839: AND Operation IN ( 'LOP_DELETE_ROWS' )
840: /*Use this subquery to filter the date*/
841: AND [TRANSACTION ID] IN (
842: SELECT DISTINCT
843: [TRANSACTION ID]
844: FROM sys.fn_dblog(NULL, NULL)
845: WHERE Context IN ( 'LCX_NULL' )
846: AND Operation IN ( 'LOP_BEGIN_XACT' )
847: AND [Transaction Name] IN ('DELETE', 'user_transaction')
848: AND CONVERT(NVARCHAR(11), [Begin Time]) BETWEEN @Date_From
849: AND
850: @Date_To )
851:
852: /****************************************/
853:
854: OPEN Page_Data_Cursor
855:
856: FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,
857: @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context
858:
859: WHILE @@FETCH_STATUS = 0
860: BEGIN
861: DECLARE @hex_pageid AS VARCHAR(MAX)
862: /*Page ID contains File Number and page number It looks like 0001:00000130.
863: In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/
864: SET @Fileid = SUBSTRING(@ConsolidatedPageID, 0,
865: CHARINDEX(':', @ConsolidatedPageID)) -- Seperate File ID from Page ID
866:
867: SET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID,
868: CHARINDEX(':',
869: @ConsolidatedPageID)
870: + 1, LEN(@ConsolidatedPageID)) ---Seperate the page ID
871: SELECT @Pageid = CONVERT(INT, CAST('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )',
872: 'varbinary(max)')) -- Convert Page ID from hex to integer
873: FROM ( SELECT CASE SUBSTRING(@hex_pageid, 1, 2)
874: WHEN '0x' THEN 3
875: ELSE 0
876: END
877: ) AS t ( pos )
878:
879: IF @Context = 'LCX_PFS'
880: BEGIN
881: DELETE @temppagedata
882: INSERT INTO @temppagedata
883: EXEC
884: ( 'DBCC PAGE(' + @Database_Name + ', '
885: + @Fileid + ', ' + @Pageid
886: + ', 1) with tableresults,no_infomsgs;'
887: );
888: INSERT INTO @pagedata
889: SELECT @ConsolidatedPageID ,
890: @Fileid ,
891: @Pageid ,
892: @AllocUnitID ,
893: [ParentObject] ,
894: [Object] ,
895: [Field] ,
896: [Value]
897: FROM @temppagedata
898: END
899: ELSE
900: IF @Context = 'LCX_TEXT_MIX'
901: BEGIN
902: INSERT INTO @ModifiedRawData
903: SELECT @ConsolidatedPageID ,
904: @Fileid ,
905: @Pageid ,
906: @Slotid ,
907: @AllocUnitID ,
908: NULL ,
909: 0 ,
910: CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX,
911: 11, 2)))) ,
912: @LCX_TEXT_MIX ,
913: @LinkID ,
914: 0
915: END
916: FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,
917: @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @Context
918: END
919:
920: CLOSE Page_Data_Cursor
921: DEALLOCATE Page_Data_Cursor
922:
923: DECLARE @Newhexstring VARCHAR(MAX);
924:
925: --The data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
926: --This hex value is in string format
927: INSERT INTO @ModifiedRawData
928: ( [PAGE ID] ,
929: [FILE IDS] ,
930: [PAGE IDS] ,
931: [Slot ID] ,
932: [AllocUnitId] ,
933: [RowLog Contents 0_var] ,
934: [RowLog Length]
935: )
936: SELECT [Page ID] ,
937: [FILE IDS] ,
938: [PAGE IDS] ,
939: SUBSTRING([ParentObject],
940: CHARINDEX('Slot', [ParentObject]) + 4,
941: ( CHARINDEX('Offset', [ParentObject])
942: - ( CHARINDEX('Slot', [ParentObject]) + 4 ) )
943: - 2) AS [Slot ID] ,
944: [AllocUnitId] ,
945: SUBSTRING(( SELECT REPLACE(STUFF(( SELECT
946: REPLACE(SUBSTRING([Value],
947: CHARINDEX(':',
948: [Value]) + 1,
949: CHARINDEX('†',
950: [Value])
951: - CHARINDEX(':',
952: [Value])), '†',
953: '')
954: FROM @pagedata C
955: WHERE B.[Page ID] = C.[Page ID]
956: AND SUBSTRING(B.[ParentObject],
957: CHARINDEX('Slot',
958: B.[ParentObject])
959: + 4,
960: ( CHARINDEX('Offset',
961: B.[ParentObject])
962: - ( CHARINDEX('Slot',
963: B.[ParentObject])
964: + 4 ) )) = SUBSTRING(C.[ParentObject],
965: CHARINDEX('Slot',
966: C.[ParentObject])
967: + 4,
968: ( CHARINDEX('Offset',
969: C.[ParentObject])
970: - ( CHARINDEX('Slot',
971: C.[ParentObject])
972: + 4 ) ))
973: AND [Object] LIKE '%Memory Dump%'
974: ORDER BY '0x'
975: + LEFT([Value],
976: CHARINDEX(':',
977: [Value]) - 1)
978: FOR
979: XML PATH('')
980: ), 1, 1, ''), ' ', '')
981: ), 1, 20000) AS [Value] ,
982: SUBSTRING(( SELECT '0x'
983: + REPLACE(STUFF(( SELECT
984: REPLACE(SUBSTRING([Value],
985: CHARINDEX(':',
986: [Value]) + 1,
987: CHARINDEX('†',
988: [Value])
989: - CHARINDEX(':',
990: [Value])), '†',
991: '')
992: FROM
993: @pagedata C
994: WHERE
995: B.[Page ID] = C.[Page ID]
996: AND SUBSTRING(B.[ParentObject],
997: CHARINDEX('Slot',
998: B.[ParentObject])
999: + 4,
1000: ( CHARINDEX('Offset',
1001: B.[ParentObject])
1002: - ( CHARINDEX('Slot',
1003: B.[ParentObject])
1004: + 4 ) )) = SUBSTRING(C.[ParentObject],
1005: CHARINDEX('Slot',
1006: C.[ParentObject])
1007: + 4,
1008: ( CHARINDEX('Offset',
1009: C.[ParentObject])
1010: - ( CHARINDEX('Slot',
1011: C.[ParentObject])
1012: + 4 ) ))
1013: AND [Object] LIKE '%Memory Dump%'
1014: ORDER BY '0x'
1015: + LEFT([Value],
1016: CHARINDEX(':',
1017: [Value]) - 1)
1018: FOR
1019: XML PATH('')
1020: ), 1, 1, ''), ' ', '')
1021: ), 7, 4) AS [Length]
1022: FROM @pagedata B
1023: WHERE [Object] LIKE '%Memory Dump%'
1024: GROUP BY [Page ID] ,
1025: [FILE IDS] ,
1026: [PAGE IDS] ,
1027: [ParentObject] ,
1028: [AllocUnitId]--,[Current LSN]
1029: ORDER BY [Slot ID]
1030:
1031: UPDATE @ModifiedRawData
1032: SET [RowLog Len] = CONVERT(VARBINARY(8000), REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))',
1033: 'varbinary(Max)')))
1034: FROM @ModifiedRawData
1035: WHERE [LINK ID] = 0
1036:
1037: UPDATE @ModifiedRawData
1038: SET [RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))',
1039: 'varbinary(Max)')
1040: FROM @ModifiedRawData
1041: WHERE [LINK ID] = 0
1042:
1043: UPDATE B
1044: SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL
1045: AND C.[RowLog Contents 0] IS NOT NULL
1046: THEN A.[RowLog Contents 0]
1047: + C.[RowLog Contents 0]
1048: WHEN A.[RowLog Contents 0] IS NULL
1049: AND C.[RowLog Contents 0] IS NOT NULL
1050: THEN C.[RowLog Contents 0]
1051: WHEN A.[RowLog Contents 0] IS NOT NULL
1052: AND C.[RowLog Contents 0] IS NULL
1053: THEN A.[RowLog Contents 0]
1054: END ) ,
1055: B.[Update] = ISNULL(B.[Update], 0) + 1
1056: FROM @ModifiedRawData B
1057: LEFT JOIN @ModifiedRawData A ON A.[PAGE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1058: 15 + 14, 2))))
1059: AND A.[FILE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1060: 19 + 14, 2))))
1061: AND A.[LINK ID] = B.[LINK ID]
1062: LEFT JOIN @ModifiedRawData C ON C.[PAGE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1063: 27 + 14, 2))))
1064: AND C.[FILE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1065: 31 + 14, 2))))
1066: AND C.[LINK ID] = B.[LINK ID]
1067: WHERE ( A.[RowLog Contents 0] IS NOT NULL
1068: OR C.[RowLog Contents 0] IS NOT NULL
1069: )
1070:
1071:
1072: UPDATE B
1073: SET B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL
1074: AND C.[RowLog Contents 0] IS NOT NULL
1075: THEN A.[RowLog Contents 0]
1076: + C.[RowLog Contents 0]
1077: WHEN A.[RowLog Contents 0] IS NULL
1078: AND C.[RowLog Contents 0] IS NOT NULL
1079: THEN C.[RowLog Contents 0]
1080: WHEN A.[RowLog Contents 0] IS NOT NULL
1081: AND C.[RowLog Contents 0] IS NULL
1082: THEN A.[RowLog Contents 0]
1083: END )
1084: --,B.[Update]=ISNULL(B.[Update],0)+1
1085: FROM @ModifiedRawData B
1086: LEFT JOIN @ModifiedRawData A ON A.[PAGE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1087: 15 + 14, 2))))
1088: AND A.[FILE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1089: 19 + 14, 2))))
1090: AND A.[LINK ID] <> B.[LINK ID]
1091: AND B.[Update] = 0
1092: LEFT JOIN @ModifiedRawData C ON C.[PAGE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1093: 27 + 14, 2))))
1094: AND C.[FILE IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],
1095: 31 + 14, 2))))
1096: AND C.[LINK ID] <> B.[LINK ID]
1097: AND B.[Update] = 0
1098: WHERE ( A.[RowLog Contents 0] IS NOT NULL
1099: OR C.[RowLog Contents 0] IS NOT NULL
1100: )
1101:
1102: UPDATE @ModifiedRawData
1103: SET [RowLog Contents 0] = ( CASE WHEN [RowLog Len] >= 8000
1104: THEN SUBSTRING([RowLog Contents 0],
1105: 15, [RowLog Len])
1106: WHEN [RowLog Len] < 8000
1107: THEN SUBSTRING([RowLog Contents 0],
1108: 15 + 6,
1109: CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents 0],
1110: 15, 6)))))
1111: END )
1112: FROM @ModifiedRawData
1113: WHERE [LINK ID] = 0
1114:
1115: UPDATE @ColumnNameAndData
1116: SET [hex_Value] = [RowLog Contents 0]
1117: --,A.[Update]=A.[Update]+1
1118: FROM @ColumnNameAndData A
1119: INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_Value],
1120: 17, 4)))) = [PAGE IDS]
1121: AND CONVERT(INT, SUBSTRING([hex_Value],
1122: 9, 2)) = B.[LINK ID]
1123: WHERE [system_type_id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )
1124: AND [LINK ID] <> 0
1125:
1126: UPDATE @ColumnNameAndData
1127: SET [hex_Value] = ( CASE WHEN B.[RowLog Contents 0] IS NOT NULL
1128: AND C.[RowLog Contents 0] IS NOT NULL
1129: THEN B.[RowLog Contents 0]
1130: + C.[RowLog Contents 0]
1131: WHEN B.[RowLog Contents 0] IS NULL
1132: AND C.[RowLog Contents 0] IS NOT NULL
1133: THEN C.[RowLog Contents 0]
1134: WHEN B.[RowLog Contents 0] IS NOT NULL
1135: AND C.[RowLog Contents 0] IS NULL
1136: THEN B.[RowLog Contents 0]
1137: END )
1138: --,A.[Update]=A.[Update]+1
1139: FROM @ColumnNameAndData A
1140: LEFT JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_Value],
1141: 5, 4)))) = B.[PAGE IDS]
1142: AND B.[LINK ID] = 0
1143: LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_Value],
1144: 17, 4)))) = C.[PAGE IDS]
1145: AND C.[LINK ID] = 0
1146: WHERE [system_type_id] IN ( 99, 167, 175, 231, 239, 241, 165, 98 )
1147: AND ( B.[RowLog Contents 0] IS NOT NULL
1148: OR C.[RowLog Contents 0] IS NOT NULL
1149: )
1150:
1151: UPDATE @ColumnNameAndData
1152: SET [hex_Value] = [RowLog Contents 0]
1153: --,A.[Update]=A.[Update]+1
1154: FROM @ColumnNameAndData A
1155: INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_Value],
1156: 9, 4)))) = [PAGE IDS]
1157: AND CONVERT(INT, SUBSTRING([hex_Value],
1158: 3, 2)) = [LINK ID]
1159: WHERE [system_type_id] IN ( 35, 34, 99 )
1160: AND [LINK ID] <> 0
1161:
1162: UPDATE @ColumnNameAndData
1163: SET [hex_Value] = [RowLog Contents 0]
1164: --,A.[Update]=A.[Update]+10
1165: FROM @ColumnNameAndData A
1166: INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_Value],
1167: 9, 4)))) = [PAGE IDS]
1168: WHERE [system_type_id] IN ( 35, 34, 99 )
1169: AND [LINK ID] = 0
1170:
1171: UPDATE @ColumnNameAndData
1172: SET [hex_Value] = [RowLog Contents 0]
1173: --,A.[Update]=A.[Update]+1
1174: FROM @ColumnNameAndData A
1175: INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_Value],
1176: 15, 4)))) = [PAGE IDS]
1177: WHERE [system_type_id] IN ( 35, 34, 99 )
1178: AND [LINK ID] = 0
1179:
1180: UPDATE @ColumnNameAndData
1181: SET [hex_Value] = 0xFFFE + SUBSTRING([hex_Value], 9, LEN([hex_Value]))
1182: --,[Update]=[Update]+1
1183: WHERE [system_type_id] = 241
1184:
1185: CREATE TABLE [#temp_Data]
1186: (
1187: [FieldName] VARCHAR(MAX) ,
1188: [FieldValue] NVARCHAR(MAX) ,
1189: [RowLogContents] VARBINARY(8000) ,
1190: [Row ID] INT ,
1191: [Transaction ID] VARCHAR(100) ,
1192: [Deletion Date Time] DATETIME ,
1193: [Deleted By User Name] VARCHAR(MAX)
1194: )
1195:
1196: INSERT INTO #temp_Data
1197: SELECT NAME ,
1198: CASE WHEN system_type_id IN ( 231, 239 )
1199: THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --NVARCHAR ,NCHAR
1200: WHEN system_type_id IN ( 167, 175 )
1201: THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --VARCHAR,CHAR
1202: WHEN system_type_id IN ( 35 )
1203: THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), hex_Value))) --Text
1204: WHEN system_type_id IN ( 99 )
1205: THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), hex_Value))) --nText
1206: WHEN system_type_id = 48
1207: THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE(hex_Value)))) --TINY INTEGER
1208: WHEN system_type_id = 52
1209: THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGER
1210: WHEN system_type_id = 56
1211: THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
1212: WHEN system_type_id = 127
1213: THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
1214: WHEN system_type_id = 61
1215: THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --DATETIME
1216: WHEN system_type_id = 58
1217: THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIME
1218: WHEN system_type_id = 108
1219: THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)
1220: + CONVERT(VARBINARY(1), xscale))
1221: + CONVERT(VARBINARY(1), 0) + hex_Value)) --- NUMERIC
1222: WHEN system_type_id = 106
1223: THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)
1224: + CONVERT(VARBINARY(1), xscale))
1225: + CONVERT(VARBINARY(1), 0) + hex_Value)) --- DECIMAL
1226: WHEN system_type_id IN ( 60, 122 )
1227: THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEY
1228: WHEN system_type_id = 104
1229: THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), hex_Value)
1230: % 2)) -- BIT
1231: WHEN system_type_id = 62
1232: THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))
1233: * ( 1.0
1234: + ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
1235: & 0x000FFFFFFFFFFFFF )
1236: * POWER(CAST(2 AS FLOAT),
1237: -52) )
1238: * POWER(CAST(2 AS FLOAT),
1239: ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
1240: & 0x7ff0000000000000 )
1241: / EXP(52 * LOG(2))
1242: - 1023 ))), 53,
1243: LEN(hex_Value)))) --- FLOAT
1244: WHEN system_type_id = 59
1245: THEN LEFT(LTRIM(STR(CAST(SIGN(CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT))
1246: * ( 1.0
1247: + ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)
1248: & 0x007FFFFF )
1249: * POWER(CAST(2 AS REAL), -23) )
1250: * POWER(CAST(2 AS REAL),
1251: ( ( ( CAST(CONVERT(VARBINARY(8000), REVERSE(hex_Value)) AS INT) )
1252: & 0x7f800000 )
1253: / EXP(23 * LOG(2))
1254: - 127 )) AS REAL), 23,
1255: 23)), 8) --Real
1256: WHEN system_type_id IN ( 165, 173 )
1257: THEN ( CASE WHEN CHARINDEX(0x,
1258: CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1259: 'VARBINARY(8000)')) = 0
1260: THEN '0x'
1261: ELSE ''
1262: END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1263: 'varchar(max)') -- BINARY,VARBINARY
1264: WHEN system_type_id = 34
1265: THEN ( CASE WHEN CHARINDEX(0x,
1266: CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1267: 'VARBINARY(8000)')) = 0
1268: THEN '0x'
1269: ELSE ''
1270: END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1271: 'varchar(max)') --IMAGE
1272: WHEN system_type_id = 36
1273: THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value)) --UNIQUEIDENTIFIER
1274: WHEN system_type_id = 231
1275: THEN CONVERT(VARCHAR(MAX), CONVERT(nvarchar(128), hex_Value)) --nvarchar(128)
1276: WHEN system_type_id = 241
1277: THEN CONVERT(VARCHAR(MAX), CONVERT(XML, hex_Value)) --XML
1278: WHEN system_type_id = 189
1279: THEN ( CASE WHEN CHARINDEX(0x,
1280: CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1281: 'VARBINARY(8000)')) = 0
1282: THEN '0x'
1283: ELSE ''
1284: END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1285: 'varchar(max)') --TIMESTAMP
1286: WHEN system_type_id = 98
1287: THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
1288: 1)) = 56
1289: THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value,
1290: 3,
1291: LEN(hex_Value)))))) -- INTEGER
1292: WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
1293: 1)) = 108
1294: THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38,
1295: 20), CONVERT(VARBINARY(1), SUBSTRING(hex_Value,
1296: 3, 1))
1297: + CONVERT(VARBINARY(1), SUBSTRING(hex_Value,
1298: 4, 1))
1299: + CONVERT(VARBINARY(1), 0)
1300: + SUBSTRING(hex_Value, 5,
1301: LEN(hex_Value)))) --- NUMERIC
1302: WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
1303: 1)) = 167
1304: THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,
1305: 9,
1306: LEN(hex_Value))))) --VARCHAR,CHAR
1307: WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
1308: 1)) = 36
1309: THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),
1310: 3, 20))) --UNIQUEIDENTIFIER
1311: WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
1312: 1)) = 61
1313: THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value,
1314: 3,
1315: LEN(hex_Value))))), 100) --DATETIME
1316: WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,
1317: 1)) = 165
1318: THEN '0x'
1319: + SUBSTRING(( CASE WHEN CHARINDEX(0x,
1320: CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1321: 'VARBINARY(8000)')) = 0
1322: THEN '0x'
1323: ELSE ''
1324: END )
1325: + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))',
1326: 'varchar(max)'),
1327: 11, LEN(hex_Value)) -- BINARY,VARBINARY
1328: END )
1329: END AS FieldValue ,
1330: [RowLogContents] ,
1331: [Row ID] ,
1332: [Transaction ID] ,
1333: NULL ,
1334: NULL
1335: FROM @ColumnNameAndData
1336: ORDER BY nullbit
1337:
1338: --Find the user ID and date time
1339: UPDATE #temp_Data
1340: SET [Deleted By User Name] = SUSER_SNAME(B.[Transaction SID]),
1341: [Deletion Date Time] = [Begin Time]
1342: FROM #temp_Data A
1343: INNER JOIN fn_dblog(NULL, NULL) B ON A.[Transaction ID] = B.[Transaction ID]
1344: WHERE B.[Operation] = 'LOP_BEGIN_XACT'
1345: AND B.[Context] = 'LCX_NULL'
1346: AND B.[Transaction Name] IN ('DELETE', 'user_transaction')
1347:
1348: --Create the column name in the same order to do pivot table.
1349:
1350: DECLARE @FieldName VARCHAR(MAX)
1351: DECLARE @AdditionalField VARCHAR(MAX)
1352: SET @FieldName = STUFF(( SELECT ','
1353: + CAST(QUOTENAME([Name]) AS VARCHAR(MAX))
1354: FROM syscolumns
1355: WHERE id = OBJECT_ID(''
1356: + @SchemaName_n_TableName
1357: + '')
1358: FOR
1359: XML PATH('')
1360: ), 1, 1, '')
1361:
1362: --Finally did pivot table and get the data back in the same format.
1363:
1364: SET @AdditionalField = @FieldName
1365: + ' ,[Deleted By User Name],[Deletion Date Time]'
1366:
1367: SET @SQL = 'SELECT ' + @AdditionalField
1368: + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN ('
1369: + @FieldName + ')) AS pvt'
1370: PRINT @SQL
1371: EXEC sp_executesql @SQL
1372:
1373: GO
對這個問題有興趣的朋友，可以參考亂馬客的文章 [SQL]透過 Transaction Log(fn_dblog) 取回被刪除的資料。
