The source code of how to recover the modified data is given here. But how does it work to recover modified data is explained here. Let’s go through it step by step. The process requires nine easy steps:
Before proceeding to step one, let us use the example given below for explanation :
--Example -1 --Update [Student] Set [Student Name]='Ben' Where [SNO]=1 --Operation type will be 'LOP_MODIFY_ROW' --Example -2 --Update [Student] Set [Student Name]='Ben',[Weight]=25 Where [SNO]=1 --Operation type will be 'LOP_MODIFY_COLUMNS'
Step-1:
The first step is to pick the Slot ID and Page ID from the modified records using sys.fn_dblog(SQL Log). The reason behind this is to pass these slot IDs and page IDs in the DBCC Page function to get the modified records. But this modified records is available in hex format.
Note : SQL Server keeps each record of table in separate slot in a particular page number meaning if you have a slot ID and page number, you can trace the records.
But, the Page ID is a combination of File ID and Page number. So we need to separate it and then we need to pass it to DBCC Page function to filter the records.
SELECT [PAGE ID],[Slot ID],[AllocUnitId] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + @SchemaName_n_TableName + '')) AND Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED') AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT') AND [Transaction Name]='Update' AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
In example -1 & 2 :
Slot ID=0
And Page ID =0001:00000184
And the break up of Page ID : 0001 is file ID And
00000184 is page No. But both of them are in Hex format. So we need to convert it into integer and then pass it to DBCC Page to filter this record.
Select Convert(int,0x0001) = 1 (In Integer) Select Convert(int,0x00000184) = 388 (In Integer) --We need to pass this file ID and Page ID in DBCC page function to get the page data along with --the modified data. DBCC Page ('test',1,388) with tableresults,no_infomsgs;
OPEN Page_Data_Cursor FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @hex_pageid AS VARCHAR(Max) SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID)) SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos) DELETE @temppagedata INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;'); INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID END
Now, we have particular page data in one table along with Page ID and Alloc Unit Id. So, we can easily filter it according to the slot number to get the required data (modified data row).
Step-2:
When we filter the slot data, it is normally in multiple rows (Depends upon the amount of data), but the target is to convert it into single row for further processing.
Here is the source code to achieve this target.
DECLARE @ModifiedRawData TABLE ( [ID] INT IDENTITY(1,1), [PAGE ID] VARCHAR(MAX), [Slot ID] INT, [AllocUnitId] BIGINT, [RowLog Contents 0_var] VARCHAR(MAX), [RowLog Contents 0] VARBINARY(8000) ) --This hex value is in string format INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId] ,[RowLog Contents 0_var]) SELECT B.[PAGE ID],A.[Slot ID],A.[AllocUnitId] ,( SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([VALUE],CHARINDEX(':',[Value])+1,48),'†','') FROM @pagedata C WHERE B.[Page ID]= C.[Page ID] And A.[Slot ID] =LTRIM(RTRIM(SUBSTRING(C.[ParentObject],5,3))) And [Object] Like '%Memory Dump%' FOR XML PATH('') ),1,1,'') ,' ','') ) AS [Value] FROM sys.fn_dblog(NULL, NULL) A INNER JOIN @pagedata B On A.[PAGE ID]=B.[PAGE ID] AND A.[AllocUnitId]=B.[AllocUnitId] AND A.[Slot ID] =LTRIM(RTRIM(Substring(B.[ParentObject],5,3))) AND B.[Object] Like '%Memory Dump%' WHERE A.AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + @SchemaName_n_TableName + '')) AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED') /*Use this sub query to filter the date*/ AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) Where Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT') AND [Transaction Name]='Update' AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To) /****************************************/ GROUP BY B.[PAGE ID],A.[Slot ID],A.[AllocUnitId]--,[Transaction ID] ORDER BY [Slot ID] --But the issue is, this modified data is in string format, we need to convert it into hex format. UPDATE @ModifiedRawData SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)') FROM @ModifiedRawData
Step-3:
The next step is to get the modified records from sql server. By using the standard SQL Server function fn_blog. We need only the selected modified records from the transaction log. So we included three filters in select statement(Context, Operation & AllocUnitId).
- Context (‘LCX_CLUSTERED’and ‘LCX_HEAP’)
- Operation (‘LOP_MODIFY_ROW’,’LOP_MODIFY_COLUMNS’)
- AllocUnitId
Here is the code snippet:
Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID],[RowLog Contents 0], [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4] ,[Log Record] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + 'dbo.student' + '')) AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
AllocUnitId : Allocation Unit ID of the table name.
We filtered two types of Operation type from sql log. Given below are the details, Why SQL server create these operation types in Log and what is the difference ?:
1-LOP_MODIFY_ROW
If there is an update that might impact in variable length column values (offset) then the type will be LOP_MODIFY_ROW.
For example -1:
Update [Student] Set [Student Name]=’Ben Where [SNO]=1
2-LOP_MODIFY_COLUMNS
If there is an update that might not impact in variable length column values (offset) and the update should impact in both fixed and variable length columns then the type will be LOP_MODIFY_COLUMNS.
For example -2:
Update [Student] Set [Student Name]=’Ben’ , [Weight]=25 Where [SNO]=1
Step-4:
Before proceeding further, we need to understand that how SQL Server keeps modified records in logs.
In both operation type cases, SQL Server keeps the modified data in the form of data chunks (only the portion that is modified and its previous state). But the question is where it is located ?
1-‘LOP_MODIFY_ROW’
If the operation type is ‘LOP_MODIFY_ROW’ then we need to select [RowLog Contents 0] (contains old data (before modification)) And [RowLog Contents 1] (contains Modified data) from the sys.fn_dblog function. But these data chunks are in hex format.
For example -1:
Select [PAGE ID],[Slot ID] ,[RowLog Contents 0], [RowLog Contents 1] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + 'dbo.student' + '')) AND Operation in ('LOP_MODIFY_ROW') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED') [RowLog Contents 0]= 0x6F62 --Select Convert (varchar(max),0x6F62) [RowLog Contents 1]= 0x656E --Select Convert (varchar(max),0x656E)
2-‘LOP_MODIFY_COLUMNS’
If the operation type is ‘LOP_MODIFY_COLUMNS’ then actual data (before modification) And modified data is available in [Log Record] column in the sys.fn_dblog function. But this data in merge format and in one hex data chunk.
For example -2:
Select [PAGE ID],[Slot ID] ,[RowLog Contents 0], [RowLog Contents 1] ,[RowLog Contents 3],[RowLog Contents 4] ,[Log Record] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + 'dbo.student' + '')) AND Operation in ('LOP_MODIFY_COLUMNS') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
But, if the data is in the column [Log Record] then why we need to select other columns, basically other columns info will help us to select and separate the merge data chunk into modified and actual data from [log record] column.
Step-5:
In case of operation type ‘LOP_MODIFY_ROW’, It is very simple to recover. Now we have the modified row (we prepare it in step 2) in hex format and modified and actual data chunks. Given below is the Modified Data is in Hex format.
0x30001400010000000000000060940000460000000500E0020029002C0
053005400440030003000310042656E
And data chunks
Before Modification data : 0x6F62
After Modification data : 0x656E
Now we need to replace the matching portion of the data from modified to actual one to recover actual data.
So, after replacement we will get the old (actual values before modification) values.
0x30001400010000000000000060940000460000000500E0020029002C0
0530054004400300030003100426F62
INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[RowLog Contents 0_var]) SELECT TOP 1 @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],REPLACE ([RowLog Contents 0_var],CAST('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents1") )', 'varchar(max)'),cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents0") )', 'varchar(max)')) AS [RowLog Contents 0_var] FROM @ModifiedRawData WHERE [PAGE ID]=@ConsolidatedPageID And [Slot ID]=@Slotid And [AllocUnitId]=@AllocUnitID ORDER BY [ID] DESC UPDATE @ModifiedRawData SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)') FROM @ModifiedRawData
Finally, we recovered the data. Now we need to convert this hex values into their original data types.(readable format).
But, still the recovery is not finished because, we have operation type ‘LOP_MODIFY_COLUMNS’ to discuss.
Step-6:
If operation type is ‘LOP_MODIFY_COLUMNS’, we need to follow a different mechanism to recover the modified data.
Step-6.1:
As we discussed, the data for this operation type is in the [Log record] but in merge format, so we need to get the [log record] field data. Here is the query.
Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID],[RowLog Contents 0], [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4] ,Substring ([Log Record],[Log Record Fixed Length],([Log Record Length]+1)-([Log Record Fixed Length])) as [Log Record] ,Operation FROM sys.fn_dblog(NULL, NULL) WHERE --AllocUnitName =@SchemaName_n_TableName --'dbo.STUDENT' AllocUnitId IN (Select [Allocation_unit_id] from sys.allocation_units allocunits INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id) Where object_id=object_ID('' + @SchemaName_n_TableName + '')) AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED') /*Use this subquery to filter the date*/ AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT') AND [Transaction Name]='Update' AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
Step-6.2:
We reduced [log record] field data, because we ignored till the length of the fixed lenght data and pick the rest of the values. But still we don’t have our required data. On this log data we need to search [RowLog Contents 3] column data in Log record because exactly after [RowLog Contents 3] column data our required data is there.
SET @RowLogData_Var = SUBSTRING(@LogRecord_Var, CHARINDEX(@RowLogContents3_Var,@LogRecord_Var) +LEN(@RowLogContents3_Var) ,LEN(@LogRecord_Var)) SELECT @RowLogData_Hex=CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@RowLogData_Var"),0) )', 'varbinary(max)') FROM (SELECT CASE SUBSTRING(@RowLogData_Var, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
Example -2 :
Log record =0x00080008000400000014000100010002000200100010002A002A0001000
2000101000C00002364944E00000102000402030004464A61631962616D6F62000C656E2364
[RowLog Contents3]= 0x0101000C00002364944E00000102000402030004
Our required Data : 0x464A61631962616D6F62000C656E2364
Structure of our required data is : Actual Fixed length data chunk + Modified Fixed length data chunks + Actual Variable length data chunk + Modified Variable length data chunks
Now, we have our required data and we also know the structure of the required data but still we do not know to separate the chunks.
Step-6.3:
The required data is in @RowLogData_Hex variable. The target is to separate both fixed data and variable data with respect to actual and modified data chunks.
To achieve this, we need to know the length and start position of the chunks to separate the data.
In the [RowLog Contents 1] field, we have the length of fixed length and variable length data.
--For Fixed Length Data Chunks (The length of actual and modified data chunks will be same) SET @FixedLength_Loc= CONVERT(INT,SUBSTRING(@RowLogContents1,1,1)) Select CONVERT(INT,SUBSTRING(0x01000200,1,1)) --@FixedLength_Loc=1 Means Fixed Length is 1. -- For Variable Length Data Chunks (The length of actual and modified data chunks will be same) SET @VariableLength_Loc =CONVERT(INT,SUBSTRING(@RowLogContents1,3,1)) Select CONVERT(INT,SUBSTRING(0x01000200,3,1)) --@VariableLength_Loc=2 Means variable Length is 2. --In the [RowLog Contents 0] field, we have the start position of fixed length & variable length data --modification. -- These fixed length and variable length start positions, we will use at the time of replacement. SET @FixedLength_Offset= CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(@RowLogContents0))))--) Select CONVERT(int,CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(0x100010002A002A00))))) --@FixedLength_Offset =16 Means Start Position of modification in Fixed Length is 16. SET @VariableLength_Offset=CONVERT(int,CONVERT(BINARY(2),REVERSE(@RowLogContents0))) Select CONVERT(int,CONVERT(BINARY(2),REVERSE(0x100010002A002A00))) --@VariableLength_Offset42 Means Start Position of modification in variable Length is 42.
Step-6.4:
Now, we have the length and the start position so we can easily get the actual and modified data chunks
-- Actual Fixed length data chunk SET @FixedOldValues = SUBSTRING(@RowLogData_Hex,1,@FixedLength_Loc) Select Substring(0x464A61631962616D6F62000C656E2364,1,1)= 0x46 --One more thing we need to find out that what is the reminder in the @FixedOldValues because if there --is remaindar we need to add it to find the modified chunk. SET @FixedLengthIncrease = (CASE WHEN (Len(@FixedOldValues)%4)=0 THEN 1 ELSE (4-(LEN(@FixedOldValues)%4))+1 END) @FixedLengthIncrease=4 --Modified Fixed length data chunks SET @FixedNewValues =SUBSTRING(@RowLogData_Hex,@FixedLength_Loc+@FixedLengthIncrease,@FixedLength_Loc) Select SUBSTRING(0x464A61631962616D6F62000C656E2364,1+4,1) =0x19 -- Actual Variable length data chunk SET @VariableOldValues =SUBSTRING(@RowLogData_Hex,@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease-1),@VariableLength_Loc) Select SUBSTRING(0x464A61631962616D6F62000C656E2364,1+4+1+(4-1),2)=0x6F62 --Modified Fixed length data chunks --Same for variable increase. SET @VariableLengthIncrease = (CASE WHEN (LEN(@VariableOldValues)%4)=0 THEN 1 ELSE (4-(Len(@VariableOldValues)%4)) END) Select SUBSTRING(SUBSTRING(0x464A61631962616D6F62000C656E2364,1+4+1+(4-1)+2+2,2+1),1,LEN(0x6F62)) =0x656E
Step-6.5:
In step 2, we have the modified data
0x30001400010000000000000060940000190000000500E0020029002C0053005400440030003000310042656E and in the step 6.5 we received the chunks of actual and modifed data. Now we need to do simple replacement as per the start positions we found from [RowLog Contents 0] field.
SELECT TOP 1 @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],NULL ,SUBSTRING([RowLog Contents 0] ,0,@FixedLength_Offset+1) + CAST(REPLACE(SUBSTRING([RowLog Contents 0],@FixedLength_Offset+1,(@TotalFixedLengthData+4)-@FixedLength_Offset),@FixedNewValues, @FixedOldValues) AS VARBINARY(max)) + SUBSTRING([RowLog Contents 0],@TotalFixedLengthData+5,(@VariableLength_Offset+1)-(@TotalFixedLengthData+5)) + CAST(REPLACE(SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1,Len(@VariableNewValues)) , @VariableNewValues , @VariableOldValues) AS VARBINARY)+ SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1+Len(@VariableNewValues),Len([RowLog Contents 0])-(@VariableLength_Offset+LEN(@VariableNewValues)))
So it will be like Modified Data : 0x30001400010000000000000060940000190000000500E002002900
2C0053005400440030003000310042656E
Actual Data : 0x30001400010000000000000060940000460000000500E002002900
2C00530054004400300030003100426F62
Step-7:
Now, we have both actual and modified data in hex values, we need to follow the same steps followed under ‘how to recover deleted records’ to convert it into readable data format.
Step-8:
We have the data in actual format, Now we can identify which column has been updated and which is same. On the basis we can create an update statement that can help you to recover the modified data.
;With CTE AS (SELECT(CASE WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + A.[FieldValue]+ '''','NULL')+ ' ,'+' ' WHEN system_type_id In (231,239) THEN QUOTENAME([Name]) + '=' + ISNULL(+ 'N''' +A.[FieldValue]+ '''','NULL')+ ' ,'+'' WHEN system_type_id In (58,61,36) THEN QUOTENAME([Name]) + '=' + ISNULL(+ ''''+A.[FieldValue]+ '''','NULL') + ' ,'+' ' WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '=' + ISNULL(A.[FieldValue],'NULL')+ ' ,'+' ' END) as [Field] ,A.[Slot ID] ,A.[Transaction ID] as [Transaction ID] ,'D' AS [Type] ,[A].Rowlogcontents FROM #temp_Data AS [A] INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName] AND [A].[Slot ID]=[B].[Slot ID] AND [B].[Transaction ID]= (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM [B] AS [C] WHERE [A].[Slot ID]=[C].[Slot ID] GROUP BY [Slot ID]) INNER JOIN sys.columns [D] On [object_id]=object_id('' + @SchemaName_n_TableName + '') AND A.[Fieldname]=D.[name] WHERE ISNULL([A].[FieldValue],'')<>ISNULL([B].[FieldValue],'') UNION ALL SELECT(CASE WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + A.[FieldValue]+ '''','NULL')+ ' AND '+'' WHEN system_type_id In (231,239) THEN QUOTENAME([Name]) + '='+ ISNULL(+ 'N''' +A.[FieldValue]+ '''','NULL')+ ' AND '+'' WHEN system_type_id In (58,61,36) THEN QUOTENAME([Name]) + '=' + ISNULL(+ ''''+A.[FieldValue]+ '''','NULL') + ' AND '+'' WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '=' + ISNULL(A.[FieldValue],'NULL') + ' AND '+'' END) AS [Field] ,A.[Slot ID] ,A.[Transaction ID] AS [Transaction ID] ,'S' AS [Type] ,[A].Rowlogcontents FROM #temp_Data AS [A] INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName] AND [A].[Slot ID]=[B].[Slot ID] AND [B].[Transaction ID]= (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM [B] AS [C] WHERE [A].[Slot ID]=[C].[Slot ID] GROUP BY [Slot ID]) INNER JOIN sys.columns [D] ON [object_id]=object_id('' + @SchemaName_n_TableName + '') AND A.[Fieldname]=D.[name] WHERE ISNULL([A].[FieldValue],'')=ISNULL([B].[FieldValue],'') AND A.[Transaction ID] NOT IN (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM [B] AS [C] WHERE [A].[Slot ID]=[C].[Slot ID] GROUP BY [Slot ID]) ) ,CTEUpdateQuery AS (SELECT 'UPDATE ' + @SchemaName_n_TableName + ' SET ' + LEFT( STUFF((SELECT ' ' + ISNULL([Field],'')+ ' ' FROM CTE B WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,''), LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE B WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,'') )-3) + ' WHERE ' + LEFT(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,'') , LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,''))-5) AS [Update Statement], [Slot ID] ,[Transaction ID] ,Rowlogcontents FROM CTE A GROUP BY [Slot ID] ,[Transaction ID] ,Rowlogcontents ) INSERT INTO #temp_Data SELECT 'UPDATE STATEMENT',[Update Statement],[Rowlogcontents],[Transaction ID],[Slot ID] FROM CTEUpdateQuery
Your observations on my posts are very much appreciated. Therefore please do comment.
Hi, thank you very much for this great post! It was really usefull to me, but I found a little bug:
I have a situation with LOP_MODIFY_COLUMNS, but @FixedNewValues and @FixedOldValues both have value 0x00, and the REPLACE statement on line 252 (in the full code in the previous post) hangs in an infinite loop.
What is to be done in such situation? I tried to remove the REPLACE statement and replace it with [RowLog Contents 0], but it does not work 😦
I suppose this situation happens when all updated columns in the table are of variable length.
Hi Radoslav
Thank you for your feedback.Let me explain this operation types.
If operation type is LOP_MODIFY_COLUMNS, means
you have executed an update statement having both variable and fixed length column. For Example :
If you execute below statement the operation type will be [LOP_MODIFY_COLUMNS]
Update [Student] Set [Student Name]=’Bob jerry’,[Student ID]=’STD004′
,[SNo]=4
Where [SNO] =2
If you execute below statement the operation type will be [LOP_MODIFY_ROW]
Update [Student] Set [Student Name]=’Bob jerry’,[Student ID]=’STD004′
-–,[SNo]=4
Where [SNO] =2
Unfortunalty,I could not regenerate this bug because in case of LOP_MODIFY_COLUMNS the fixed column value data should be there.
Kindly post [RowLog Contents 0] ,[RowLog Content1], [RowLog Content2], [RowLog Content3], [RowLog Content4],[Log Record] data to debug it.
Also, post your sql server environment details
Thank you.
Imran
Hi Muhammad,
Thank you for your answer. Unfortunately I could not reproduce the bug, but I found also something else. When the table I try to recover has a clustered primary key, the update statement returned from your SP is NULL. For example, if you create the table with:
CREATE TABLE [dbo].[Student](
[Sno] [int] NOT NULL,
[Student ID] [nvarchar](6) NOT NULL,
[Student name] [varchar](50) NOT NULL,
[Date of Birth] [datetime] NOT NULL,
[Weight] [int] NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[Sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I noticed that then in the Transaction Log context is LCX_CLUSTERED instead of LCX_HEAP in your original example, and that replacing @FixedNewValues with @FixedOldValues (respectivelly @VariableOldValues with @VariableNewValues) does not change anything, and the new values are retrieved only for the modified record.
Hi Radoslav,
I fixed this issue.Thanks.
Imran
I forgot to mention – SQL Server 2008 Express, 64-bit.
Well pointed out Radoslav, much appreciated. Working on this, shall keep you posted shortly.
Imran
I am having a problem knowing which variable length column was updated with a given schema. With LOP_MODIFY_ROW this is no big deal because I have the column schema at my disposal.
With LOP_MODIFY_COLUMNS it is a problem because I only get an offset of the variable row. Fixed columns are easy (again because I have the schema). But with variable columns this means I must know the value at the time of update of variable length columns before it (e.g. knowing that ‘STD001’ is 6 bytes in your example so @VariableLength_Offset can tell me I am updating the “Student name” column).
I do not want to use the page, because it appears to get overwritten if several updates occur. Assuming I have a LOP_MODIFY_COLUMNS at some point in the past (and the record has since been updated several times), is there any way I can tell the variable column the update represents? Do I have to work backwards?
Hi George,
Fixed this issue.Given is the sample to test your case.
Update [Student] Set [Student ID]=’STD003′ ,[Weight]=1 Where [Sno]=2
Update [Student] Set [Student ID]=’STD03′ ,[Weight]=2 Where [Sno]=2
Update [Student] Set [Student ID]=’STD3′ ,[Weight]=3 Where [Sno]=2
Update [Student] Set [Student ID]=’ST3′ ,[Weight]=4 Where [Sno]=2
Update [Student] Set [Student ID]=’S3′ ,[Weight]=5 Where [Sno]=2
Please check.
Thank you.
Imran
I’m glad that you also started contributing in the community.
stay blessed.
Zahir
Thank you Zahir.
HI Muhammad
This was very useful. There was an accidental update for about 12k records. And this recovery takes a long time. I need to know who (from which Id) the update statement was executed and the exact update statement. Is this possible?
Hi Bala,
Thank you for your feedback. I need to check your both requirements . IF it is possible, I will include it in the script.
Imran
The link ‘https://raresql.wordpress.com/2012/01/31/how-to-recover%E2%80%A6-server-part-1’ displays an error.
Hi Manish,
Thank you for your feedback. Here is the actual link https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/
, Also updated in the post.
Imran
Hi Muhammad Imran,
This is Abdul Mutallib again.
Recover_Modified_Data_Proc procedure is working fine as long as I update the records by using where clause. There is also possibilities that, we may update the table without using where clause(intentionally or by mistake). In with “where clause recovering the udpates”, “without where clause not able to recover it”. Please could you update the script to make it work in second scenario.
Hi Abdul,
Thank you for your feed back. I explained the second scenario in this article (https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/)
Please post few sample if this scenario is not working for you.
Imran
Hi Muhammad Imran, Thank you very much for your solution. Presently I am working with some assignment. I will check and get back to you tommorrow.
Hi Imran,
Thank you very much for your post. and the way your explanation is very good to understand. this is very useful for me.
This was awesome. Thank you for sharing.
Hey Imran ….I am not good at sql scripting …so can you define your variables as a lot of undefined variable errors are coming when i executed your code.
Hi Raghvendra,
I think the script is not copied properly.
Imran
I ran this example on SQL 2008 R2 and it returned zero rows…any idea why?
Scott
Hi Scott,
Please check the log. I think there is no relevant data in the log. (Select * from sys.fn_dblog(NULL, NULL))
Thanks
Imran
Hi, Excellent Article. Appreciate your hard work.
Ramkumar
http://www.sqlservercentral.com/blogs/livingforsqlserver/
https://www.facebook.com/#!/LivingForSqlServer
This structure that crudely defines the structure of the field [Log Record] from the return of fn_dblog() may prove useful:
// Partly from http://www.sqlservercentral.com/blogs/livingforsqlserver/2012/11/27/time-pass-with-transaction-log-part-5-insert/
// However, there are 7 bytes between element count and rowlog contents lengths
typedef struct log_record_header { // For LOP_MODIFY_COLUMN event
unsigned char LogRecordFixedLength[3];
unsigned char PreviousLSN[10];
unsigned char FlagBits[2]; // Unsure
unsigned char TransactionID[6];
unsigned char LogFileID_Maybe[1]; // Unsure
unsigned char PageID[5];
unsigned char AllocationUnitID[8];
unsigned char LSN[9]; // Links FORMAT_PAGE_LSN
unsigned char PartitionID[8];
unsigned char RowFlag[2];
unsigned char NumElements[2];
unsigned char Bytes[7]; // Unsure of the meaning
unsigned char LengthOfRowLogContents0[2];
unsigned char LengthOfRowLogContents1[2];
unsigned char LengthOfRowLogContents2[2];
unsigned char LengthOfRowLogContents3[2];
unsigned char LengthOfRowLogContents4[2];
unsigned char Remainder[8000]; // Actual rowlog hunks go here, end to end
} LogRecordHeader, *pLogRecordHeader;
The reason I think it could prove helpful is that searching for the string of one of the rowlog contents fields within the Log Record could produce a false match (suppose that the same pattern exists by accident in an earlier section of the log record. While this is improbable, Murphy’s law says it will happen at the wors possible moment.
The last length entry (for RowLog Contents4) does not seem to be correct. This is from the debugger:
RowLog_Contents_0 0x003ac76c “1000100029002900” char [8001]
RowLog_Contents_1 0x003aa820 “01000100” char [8001]
RowLog_Contents_3 0x003a6988 “0101000C0000E7A4787D00000102000402030004” char [8001]
RowLog_Contents_4 0x003a4a3c “280000001901000C4200E7A45A7D0000” char [8001]
pLogRec->LengthOfRowLogContents0,mb 0x00396f67 00 08 00 04 00 00 00 14 00 01 00 01 00 01 00 01
We can see that there are 8 bytes for [RowLog Contents 0] (correct)
We can see that there are 4 bytes for [RowLog Contents 1](correct)
[RowLog Contents 2] is empty and has 0 bytes
We can see that there are 20 bytes for [RowLog Contents 3] (16+4)(correct)
We can see that there is 1 byte listed for [RowLog Contents 4] which has a length of 16 bytes
Hi Muhammad,
I have reviewing your solution to recovering Modify Records, and have a question LOP_MODIFY_COLUMNS. When Step 2 is executed, it will retrieve the most current PAGE. Let’s suppose the same record was modified a number of times. Will there be a problem in that the actual data in the PAGE does not match what is in the LOP_MODIFY_COLUMNS LogRecord, because it has been updated a number of times? I am trying to understand.
Thank you.
Hi Mike,
Very nice question. Basically data pages always contains the updated records. Let me explain it with the help of example.
Lets say you have data ‘A’ in a column of a table then you updated it to ‘B’ and later on ‘C’
Now ‘C’ will be available in data page. and ‘A’ and ‘B’ will be available in the log with its previous values.
Meaning ‘B’ is the old value of ‘C’ and ‘A’ is the old value of ‘B’.
If we need to recover records, we will check what is the current value and what was the previous value and will make a self join of current and previous value.
So in this way we can even recover n number of times updated records as well.
Thank you.
Imran
Arsalan
I appreciate your Efforts and the way of understanding. I executed this proc for many tables it worked fine but for few it shows following Error.
“Invalid length parameter passed to the LEFT or SUBSTRING function”
i hope u will send me the updated one..
Thank you.
Hi Arsalan,
Thank you for your valuable feedback. Please send me some sample data, where it generates error also send me the SQL server environment including collation details.
Thank you
Imran
What happens if there has been a page split? How does one recover the records if the data is now in a different page?
It usually happens when you have BLOB data types in your table. This script is not compatible with the BLOB data types.
I am still researching on how to recover modified records if table has BLOB data type.
Imran
It can also happen just because of a page split. For instance, you may have a nearly full page and after the update occurred, someone might insert records into the page. If the page is too full, then the page will split.
Yes, you are right.
I try to use your procedure, but, when I execute the procedure, the returning is nothing, but, when I use a ApexSQL or MDFview, I can see the old values, why ?
Hi Alexandre,
Please let me know your SQL Server version, collation and table’s data type. I can check the problem and revert to you.
Thanks,
Imran
Hello,
I have read both procedures Recover_Deleted_Data_Proc and Recover_Modified_Data_Proc and they work like charm.
but i have a problem that if a user updates or delete a record the trace disappears from fn_dblog() after a little while i have already set my database recovery model to full and still the records from fn_dblog disappear after a while (no backup or recovery were made for the database).
Do you have any idea why the records suddenly disappear ?
Thank you
Hi,
Great work!
I just want to ask, will this work with SQL 2008 r2 64bit and the table I want to check is only inserted/updated by a trigger from another table?
Can I see the inserts and updates made by the trigger on the table?
Thanks
[…] How to create undo update statements […]
good one
[…] How to recover modified records from SQL Server … – How to recover modified records from SQL Server without Backup Explanation. February 1, 2012 by Muhammad Imran […]