In my previous post “How to recover deleted records from SQL Server”, I received requests to “develop a mechanism that can recover the modified records from SQL Server”
Now, it is very easy to recover modified data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).
- tinyint
- smallint
- int
- bigint
- bit
- char
- varchar
- nchar
- nvarchar
- datetime
- smalldatetime
- money
- smallmoney
- decimal
- numeric
- real
- float
- binary
- varbinary
- uniqueidentifier
Let me explain this issue demonstrating simple example.
--Create Table 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)
--Inserting data into table Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',40) Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',35) --Check the existence of the data Select * from dbo.[Student]
--By mistake if all records are updated instead of one record Update [Student] Set [Student Name]='Bob jerry' --Where [SNO]=1 forget to use where clause --Verify the data has been modified Select * from dbo.[Student]
Now, you need to create this procedure to recover your modified data
Create PROCEDURE Recover_Modified_Data_Proc
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(MAX),
@Date_From datetime='1900/01/01',
@Date_To datetime ='9999/12/31'
AS
DECLARE @parms nvarchar(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @RowLogContents0 VARBINARY(8000)
DECLARE @RowLogContents1 VARBINARY(8000)
DECLARE @RowLogContents3 VARBINARY(8000)
DECLARE @RowLogContents3_Var VARCHAR(MAX)
DECLARE @RowLogContents4 VARBINARY(8000)
DECLARE @LogRecord VARBINARY(8000)
DECLARE @LogRecord_Var VARCHAR(MAX)
DECLARE @ConsolidatedPageID VARCHAR(MAX)
Declare @AllocUnitID as bigint
Declare @TransactionID as VARCHAR(MAX)
Declare @Operation as VARCHAR(MAX)
Declare @DatabaseCollation VARCHAR(MAX)
/* Pick The actual data
*/
declare @temppagedata table
(
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)
declare @pagedata table
(
[Page ID] sysname,
[AllocUnitId] bigint,
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)
DECLARE Page_Data_Cursor CURSOR FOR
/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
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')
/*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)
/****************************************/
GROUP BY [PAGE ID],[Slot ID],[AllocUnitId]
ORDER BY [Slot ID]
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)
/*Page ID contains File Number and page number It looks like 0001:00000130.
In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/
SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID)) ---Seperate the page ID
SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) -- Convert Page ID from hex to integer
FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
DELETE @temppagedata
-- Now we need to get the actual data (After modification) from the page
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;');
-- Add Page Number and allocUnit ID in data to identity which one page it belongs to.
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);
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)
)
--The modified data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
--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%'
Group By [Value] 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 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)
/****************************************/
GROUP BY B.[PAGE ID],A.[Slot ID],A.[AllocUnitId]--,[Transaction ID]
ORDER BY [Slot ID]
-- Convert the hex value data in string, convert it into Hex value as well.
UPDATE @ModifiedRawData SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
FROM @ModifiedRawData
---Now we have modifed data plus its slot ID , page ID and allocunit as well.
--After that we need to get the old values before modfication, these datas are in chunks.
DECLARE Page_Data_Cursor CURSOR FOR
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 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)
/****************************************/
Order By [Slot ID],[Transaction ID] DESC
OPEN Page_Data_Cursor
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@TransactionID,@RowLogContents0,@RowLogContents1,@RowLogContents3,@RowLogContents4,@LogRecord,@Operation
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Operation ='LOP_MODIFY_ROW'
BEGIN
/* If it is @Operation Type is 'LOP_MODIFY_ROW' then it is very simple to recover the modified data. The old data is in [RowLog Contents 0] Field and modified data is in [RowLog Contents 1] Field. Simply replace it with the modified data and get the old data.
*/
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 (UPPER([RowLog Contents 0_var]),UPPER(CAST('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents1") )', 'varchar(max)')),UPPER(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
--- Convert the old data which is in string format to 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 Where [Slot ID]=@SlotID
END
IF @Operation ='LOP_MODIFY_COLUMNS'
BEGIN
/* If it is @Operation Type is 'LOP_MODIFY_ROW' then we need to follow a different procedure to recover modified
.Because this time the data is also in chunks but merge with the data log.
*/
--First, we need to get the [RowLog Contents 3] Because in [Log Record] field the modified data is available after the [RowLog Contents 3] data.
SET @RowLogContents3_Var=cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents3") )', 'varchar(max)')
SET @LogRecord_Var =cast('' AS XML).value('xs:hexBinary(sql:variable("@LogRecord"))', 'varchar(max)')
DECLARE @RowLogData_Var VARCHAR(Max)
DECLARE @RowLogData_Hex VARBINARY(Max)
---First get the modifed data chunks in string format
SET @RowLogData_Var = SUBSTRING(@LogRecord_Var, CHARINDEX(@RowLogContents3_Var,@LogRecord_Var) +LEN(@RowLogContents3_Var) ,LEN(@LogRecord_Var))
--Then convert it into the hex values.
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)
DECLARE @TotalFixedLengthData INT
DECLARE @FixedLength_Offset INT
DECLARE @VariableLength_Offset INT
DECLARE @VariableLength_Offset_Start INT
DECLARE @VariableLengthIncrease INT
DECLARE @FixedLengthIncrease INT
DECLARE @OldFixedLengthStartPosition INT
DECLARE @FixedLength_Loc INT
DECLARE @VariableLength_Loc INT
DECLARE @FixedOldValues VARBINARY(MAX)
DECLARE @FixedNewValues VARBINARY(MAX)
DECLARE @VariableOldValues VARBINARY(MAX)
DECLARE @VariableNewValues VARBINARY(MAX)
-- Before recovering the modfied data we need to get the total fixed length data size and start position of the varaible data
SELECT TOP 1 @TotalFixedLengthData=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))
,@VariableLength_Offset_Start=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))+5+CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2)))) + 1, 2))))/8.0))
FROM @ModifiedRawData
ORDER BY [ID] DESC
SET @FixedLength_Offset= CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(@RowLogContents0))))--)
SET @VariableLength_Offset=CONVERT(int,CONVERT(BINARY(2),REVERSE(@RowLogContents0)))
/* We already have modified data chunks in @RowLogData_Hex but this data is in merge format (modified plus actual data)
So , here we need [Row Log Contents 1] field , because in this field we have the data length both the modified and actual data
so this length will help us to break it into original and modified data chunks.
*/
SET @FixedLength_Loc= CONVERT(INT,SUBSTRING(@RowLogContents1,1,1))
SET @VariableLength_Loc =CONVERT(INT,SUBSTRING(@RowLogContents1,3,1))
/*First , we need to break Fix length data actual with the help of data length */
SET @OldFixedLengthStartPosition= CHARINDEX(@RowLogContents4,@RowLogData_Hex)
SET @FixedOldValues = SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition,@FixedLength_Loc)
SET @FixedLengthIncrease = (CASE WHEN (Len(@FixedOldValues)%4)=0 THEN 1 ELSE (4-(LEN(@FixedOldValues)%4)) END)
/*After that , we need to break Fix length data modified data with the help of data length */
SET @FixedNewValues =SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease,@FixedLength_Loc)
/*Same we need to break the variable data with the help of data length*/
SET @VariableOldValues =SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease),@VariableLength_Loc)
SET @VariableLengthIncrease = (CASE WHEN (LEN(@VariableOldValues)%4)=0 THEN 1 ELSE (4-(Len(@VariableOldValues)%4))+1 END)
SET @VariableOldValues =(Case When @VariableLength_Loc =1 Then @VariableOldValues+0x00 else @VariableOldValues end)
SET @VariableNewValues =SUBSTRING(SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease-1)+@VariableLength_Loc+@VariableLengthIncrease,Len(@RowLogData_Hex)+1),1,Len(@RowLogData_Hex)+1) --LEN(@VariableOldValues)
/*here we need to replace the fixed length & variable length actaul data with modifed data
*/
Select top 1 @VariableNewValues=Case
When Charindex(Substring(@VariableNewValues,0,Len(@VariableNewValues)+1),[RowLog Contents 0])<>0 Then Substring(@VariableNewValues,0,Len(@VariableNewValues)+1)
When Charindex(Substring(@VariableNewValues,0,Len(@VariableNewValues)),[RowLog Contents 0])<>0 Then Substring(@VariableNewValues,0,Len(@VariableNewValues))
When Charindex(Substring(@VariableNewValues,0,Len(@VariableNewValues)-1),[RowLog Contents 0])<>0 Then Substring(@VariableNewValues,0,Len(@VariableNewValues)-1)--3 --Substring(@VariableNewValues,0,Len(@VariableNewValues)-1)
When Charindex(Substring(@VariableNewValues,0,Len(@VariableNewValues)-2),[RowLog Contents 0])<>0 Then Substring(@VariableNewValues,0,Len(@VariableNewValues)-2)
When Charindex(Substring(@VariableNewValues,0,Len(@VariableNewValues)-3),[RowLog Contents 0])<>0 Then Substring(@VariableNewValues,0,Len(@VariableNewValues)-3) --5--Substring(@VariableNewValues,0,Len(@VariableNewValues)-3)
End
FROM @ModifiedRawData Where [Slot ID]=@SlotID ORDER BY [ID] DESC
INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[RowLog Contents 0_var],[RowLog Contents 0])
SELECT TOP 1 @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],NULL
,CAST(REPLACE(SUBSTRING([RowLog Contents 0],0,@TotalFixedLengthData+1),@FixedNewValues, @FixedOldValues) AS VARBINARY(max))
+ SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2)
+ SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3, CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2))))/8.0)))
+ SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2))))/8.0)), 2)
+ Substring([RowLog Contents 0],@VariableLength_Offset_Start,(@VariableLength_Offset-(@VariableLength_Offset_Start-1)))
+ CAST(REPLACE(SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1,Len(@VariableNewValues))
, @VariableNewValues
, @VariableOldValues) AS VARBINARY)
+ Substring([RowLog Contents 0],@VariableLength_Offset+Len(@VariableNewValues)+1,LEN([RowLog Contents 0]))
FROM @ModifiedRawData Where [Slot ID]=@SlotID ORDER BY [ID] DESC
END
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@TransactionID,@RowLogContents0,@RowLogContents1,@RowLogContents3,@RowLogContents4,@LogRecord,@Operation
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @RowLogContents VARBINARY(8000)
Declare @AllocUnitName NVARCHAR(Max)
Declare @SQL NVARCHAR(Max)
DECLARE @bitTable TABLE
(
[ID] INT,
[Bitvalue] INT
)
----Create table to set the bit position of one byte.
INSERT INTO @bitTable
SELECT 0,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,8 UNION ALL
SELECT 4,16 UNION ALL
SELECT 5,32 UNION ALL
SELECT 6,64 UNION ALL
SELECT 7,128
--Create table to collect the row data.
DECLARE @DeletedRecords TABLE
(
[ID] INT IDENTITY(1,1),
[RowLogContents] VARBINARY(8000),
[AllocUnitID] BIGINT,
[Transaction ID] NVARCHAR(Max),
[Slot ID] INT,
[FixedLengthData] SMALLINT,
[TotalNoOfCols] SMALLINT,
[NullBitMapLength] SMALLINT,
[NullBytes] VARBINARY(8000),
[TotalNoofVarCols] SMALLINT,
[ColumnOffsetArray] VARBINARY(8000),
[VarColumnStart] SMALLINT,
[NullBitMap] VARCHAR(MAX)
)
--Create a common table expression to get all the row data plus how many bytes we have for each row.
;WITH RowData AS (
SELECT
[RowLog Contents 0] AS [RowLogContents]
,@AllocUnitID AS [AllocUnitID]
,[ID] AS [Transaction ID]
,[Slot ID] as [Slot ID]
--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] --@FixedLengthData
--[TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as [TotalNoOfCols]
--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]
--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]
--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) AS [TotalNoofVarCols]
--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END)
* 2) ELSE null END) AS [ColumnOffsetArray]
-- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)
THEN (
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))
+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) * 2))
ELSE null End AS [VarColumnStart]
From @ModifiedRawData
),
---Use this technique to repeate the row till the no of bytes of the row.
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
insert into @DeletedRecords
Select RowLogContents
,[AllocUnitID]
,[Transaction ID]
,[Slot ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
--Get the Null value against each column (1 means null zero means not null)
,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]
FROM
N4 AS Nums
Join RowData AS C ON n<=NullBitMapLength
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))
FROM RowData D
CREATE TABLE [#temp_Data]
(
[FieldName] VARCHAR(MAX) COLLATE database_default NOT NULL,
[FieldValue] VARCHAR(MAX) COLLATE database_default NULL,
[Rowlogcontents] VARBINARY(8000),
[Transaction ID] VARCHAR(MAX) COLLATE database_default NOT NULL,
[Slot ID] INT,
[NonID] INT,
--[System_type_id] int
)
---Create common table expression and join it with the rowdata table
--to get each column details
;With CTE AS (
/*This part is for variable data columns*/
SELECT
A.[ID],
Rowlogcontents,
[Transaction ID],
[Slot ID],
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
--Calculate the variable column size from the variable column offset array
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END) AS [Column value Size],
---Calculate the column length
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END) AS [Column Length]
--Get the Hexa decimal value from the RowlogContent
--HexValue of the variable column=Substring([Column value Size] - [Column Length] + 1,[Column Length])
--This is the data of your column but in the Hexvalue
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING(Rowlogcontents,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END)
- ((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) + 1,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) END AS hex_Value
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
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)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset<0
UNION
/*This part is for fixed data columns*/
SELECT
A.[ID],
Rowlogcontents,
[Transaction ID],
[Slot ID],
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],
syscolumns.length AS [Column Length]
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
,syscolumns.length) END AS hex_Value
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
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)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset>0 )
--Converting data from Hexvalue to its orgional datatype.
--Implemented datatype conversion mechanism for each datatype
--Select * from sys.columns Where [object_id]=object_id('' + @SchemaName_n_TableName + '')
--Select * from CTE
INSERT INTO #temp_Data
SELECT
NAME,
CASE
WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --NVARCHAR ,NCHAR
WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20)))) --VARCHAR,CHAR
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME
--WHEN system_type_id IN( 40) Then CONVERT(VARCHAR(MAX),CONVERT(DATE,CONVERT(VARBINARY(8000),(hex_Value))),100) --DATE This datatype only works for SQL Server 2008
WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(38,30), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- NUMERIC
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY
WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- DECIMAL
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) -- BIT
WHEN system_type_id =62 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT
When system_type_id =59 THEN Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real
WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
END AS FieldValue
,[Rowlogcontents]
,[Transaction ID]
,[Slot ID]
,[ID]
FROM CTE ORDER BY nullbit
/*Create Update statement*/
/*Now we have the modified and actual data as well*/
/*We need to create the update statement in case of recovery*/
;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,40,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
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
--And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID]= (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM #temp_Data 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,40,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
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
--And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID]= (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM #temp_Data 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 #temp_Data 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,'') )-2)
+ ' 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,''))-4)
AS [Update Statement],
[Slot ID]
,[Transaction ID]
,Rowlogcontents
,[A].[NonID]
FROM CTE A
GROUP BY [Slot ID]
,[Transaction ID]
,Rowlogcontents
,[A].[NonID] )
INSERT INTO #temp_Data
SELECT 'Update Statement',ISNULL([Update Statement],''),[Rowlogcontents],[Transaction ID],[Slot ID],[NonID] FROM CTEUpdateQuery
/**************************/
--Create the column name in the same order to do pivot table.
DECLARE @FieldName VARCHAR(max)
SET @FieldName = STUFF(
(
SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')
FOR XML PATH('')
), 1, 1, '')
--Finally did pivot table and got the data back in the same format.
--The [Update Statement] column will give you the query that you can execute in case of recovery.
SET @sql = 'SELECT ' + @FieldName + ',[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName + ',[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])'
Print @sql
EXEC sp_executesql @sql
GO
--Execute the procedure like
--Recover_Modified_Data_Proc 'Database name''Schema.table name','Date from' ,'Date to'
----EXAMPLE #1 : FOR ALL MODIFIED RECORDS
EXEC Recover_Modified_Data_Proc 'test','dbo.Student'
GO
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Modified_Data_Proc 'test','dbo.Student','2000/01/01','9999/12/31'
--It will give you the result of all modified records.
Estimated execution time for this stored procedure is 1-3 minutes.
How does it work is explained in Article -2.
Appreciate your feedback on my posts. Please do comment.
SQL Server Portal 
Hi Imran,
Nice post and it helped me to recover my data. Could you please suggest any book that can explain the sql server architecture in detail and the usage of the sys tables and object mentioned in this post. Thanks
Hi,
Thank you for your feedback, I learnt the architecture & other objects defined in Step-7 from Microsoft® SQL Server® 2008 Internals (Pro – Developer) book, Author name is Kalen Delaney . Here is the link.
http://www.amazon.com/Microsoft%C2%AE-SQL-Server%C2%AE-2008-Internals/dp/0735626243.
The rest of the details are research based.
Imran
Hi Mohd Imran
Even i have tried the code posted by you to revert back data that was accidentally updated. But it is getting executed with fail but no records are getting changed
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
What can be done on this.???
Unexpected…!
I am surprised to see if this was possible. I was not expecting to get my data back. Just because of you I was able to correct my mistake.
Thank you!
[…] Restoring Modified Rows – Sql Server […]
Hi Imran,
I tried the code but it gave me the below error.
(0 row(s) affected)
(62 row(s) affected)
(62 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(8 row(s) affected)
(2 row(s) affected)
Msg 515, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 428
Cannot insert the value NULL into column ‘FieldValue’, table ‘tempdb.dbo.#temp_Data__________________________________________________________________________________________________________00000000006D’; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
regards
Mohd Imran
Hi Imran,
Thank you for your feedback. Kindly post the table script, insert and update statement and SQL environment.
Imran
Hi Imran,
Thnaks for the response.
Here is the script.
create table salary
(
emp_id varchar(4),
salary int,
date_of_pay date
)
drop table salary
select * from salary
insert into salary values (‘E001′,20000,’2011/08/30’)
insert into salary values (‘E002′,25000,’2011/08/30’)
insert into salary values (‘E003′,20000,’2011/08/31’)
insert into salary values (‘E004′,30000,’2011/08/28’)
insert into salary values (‘E005′,28000,’2011/08/30’)
in this you can use any update statment i am getting the same issue. and here i am using SQL 2008.
Hi Imran,
I fixed this issue, It is due to “DATE” datatype which I did not include in the script.
Kindly remove the comments from line number 516 and execute the script.It will recover your modified data.
Thanks
Imran
Hello, Muhammad.
It’s a litle mistake in the part of code responseble for building undo update command – rows 588 and 569
B.[Type]=’D’ FOR XML PATH(”) ),1,1,”) )-3)
C.[Type]=’S’ FOR XML PATH(”) ),1,1,”))-5)
You’ve cut to many simbols from strings tail, so the last characters of SET and WHARE clauses of the update statement is swallowed. It’s better to change it to:
B.[Type]=’D’ FOR XML PATH(”) ),1,1,”) )-2)
C.[Type]=’S’ FOR XML PATH(”) ),1,1,”))-4)
And one more moment, Muhammad. I get error Msg 515, Level 16, State 2, Line 428
Cannot insert the value NULL into column ‘FieldValue’, table ‘tempdb.dbo.#temp_Data__________________________________________________________________________________________________________000000000090′; column does not allow nulls. INSERT fails.
after update my test table field with sring value containing single qout mark like:
set Some_Data=’some data with ” mark’
Can you help on this?
Hi Andrew,
Thank you for your feedback. Can you please post your table structure , sample data, update statement and sql environment details.
Imran
Muhammad, I’m sorry. Problem not related to qoutation marks.It’s up to delete fired on just modified row. My script to reproduce behavior:
alter database Crack_me set recovery simple
go
drop table [dbo].[TestLogs]
go
checkpoint
go
alter database Crack_me set recovery full
go
CREATE TABLE [dbo].[TestLogs](
[ID] [int] PRIMARY KEY IDENTITY NOT NULL,
[Insert_Date] [datetime] NOT NULL,
[Some_Data] [varchar](100) NOT NULL,
[Optional_Data] [varchar](50)NULL,
[Some_numbers] [int] NOT NULL,
);
GO
INSERT INTO [TestLogs]
(
Insert_Date,
Some_Data,
Optional_Data,
Some_numbers
)
VALUES (GetDate(), ‘aaa’, ‘bb’, 11);
GO
update [TestLogs] set [Some_Data]=’ccc’ where ID=1
delete from [TestLogs] where ID=1
SELECT * FROM [TestLogs];
Launching your script afterwards giving the error i’ve posted.
Hi Andrew,
You are right because this script can only recover data from modified records.But I am thinking to create another solution also that can recover the modified record even if after deletion. Will let you know the solution.
Thank you for sharing your script.
Imran
Hi imran,
when i tried to exec above proc it will generate following error while trying to update numeric field from numeric value after second attempt.
create table emp_up
(
eno numeric,
ename varchar(10),
eadd varchar(10)
)
insert into emp_up values(123,’govind’,’parel’)
update emp_up
set eno=’456′
where eno=’123′
EXEC Recover_Modified_Data_Proc ‘govind’,’dbo.emp_up’
o/p:-
123 govind parel UPDATE dbo.emp_up SET [eno]=12 WHERE [ename]=’govind’ AND [eadd]=’parel
=================================================================
update emp_up
set ename=’darshan’
where eno=’456′
EXEC Recover_Modified_Data_Proc ‘govind’,’dbo.emp_up’
o/p:-
456 govind parel UPDATE dbo.emp_up SET [ename]=’govind WHERE [eno]=456 AND [eadd]=’parel
123 govind parel UPDATE dbo.emp_up SET [eno]=123 , [ename]=’govind WHERE [eadd]=’parel
===================================================================
update emp_up
set eno=’123′
where eno=’456′
EXEC Recover_Modified_Data_Proc ‘govind’,’dbo.emp_up’
o/p:-
(0 row(s) affected)
(47 row(s) affected)
(47 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(3 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(4 row(s) affected)
Msg 515, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 428
Cannot insert the value NULL into column ‘FieldValue’, table ‘tempdb.dbo.#temp_Data__________________________________________________________________________________________________________0000000006B2’; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
Hi Govind,
I did some fixes in the code.Please check.
Also, Kindly print the output of @DeletedRecords (Table) line # 508 and CTE on line # 539.
Because some how the FieldValue (line # 534) is null either it is some other data type not handled here or the data is not available in the log. .
Imran
Hi Imran,
I tried it again as you suggested, but it did’nt work i am getting the same error so please look into it i am waiting for you response.
Hi Imran,
I sent you the procedure via email.Please try this script.
Drop table salary
GO
create table salary
(
emp_id varchar(4),
salary int,
date_of_pay date
)
insert into salary values (‘E001′,20000,’2011/08/30’)
insert into salary values (‘E002′,25000,’2011/08/30’)
insert into salary values (‘E003′,20000,’2011/08/31’)
insert into salary values (‘E004′,30000,’2011/08/28’)
insert into salary values (‘E005′,28000,’2011/08/30′)
–select * from salary
GO
Update salary Set [emp_id] =’E008′, salary=10000 Where [emp_id] =’E001′
Update salary Set [emp_id] =’E009′, salary=30000 Where [emp_id] =’E002′
Update salary Set [emp_id] =’E010′, salary=40000 Where [emp_id] =’E003′
Update salary Set [emp_id] =’E011′, salary=40000 Where [emp_id] =’E004′
Update salary Set [emp_id] =’E012′, salary=40000 Where [emp_id] =’E005′
Update salary Set [emp_id] =’E013′, salary=10000 Where [emp_id] =’E008′
Update salary Set [emp_id] =’E014′, salary=30000 Where [emp_id] =’E009′
Update salary Set [emp_id] =’E015′, salary=40000 Where [emp_id] =’E010′
Update salary Set [emp_id] =’E016′, salary=40000 Where [emp_id] =’E011′
Update salary Set [emp_id] =’E017′, salary=40000 Where [emp_id] =’E012’
GO
select * from salary
EXEC Recover_Modified_Data_Proc ‘vs2008′,’salary’
Thank you.
Imran
Hi imran
I am facing same error …..
How to print the output of @DeletedRecords (Table) line # 508 and CTE on line # 539 as you said.
Hi Govind,
I updated few more fixes.You need to write Select query in different line # of stored procedure to get the different stages of data.
e.g Select * from @DeletedRecords/ CTE
Thank you
Imran
Hi imran,
i checked on your updated proc with adding select * from @DeletedRecords at many stage(but select * from cte gives error).But following error shows.
also update statement shows different o/p.
update emp
set eno=’105′
where eno=’102′
EXEC Recover_Modified_Data_Proc ‘govind’,’dbo.emp’
o/p:-
102 govind UPDATE dbo.emp SET [eno]=102 WHERE [ename]=’govind’
===============================================================================
update emp
set ename=’nishant’
where ename=’govind’
EXEC Recover_Modified_Data_Proc ‘govind’,’dbo.emp’
o/p:-
102 govind
======================================================================================
update emp
set eno=’102′
where eno=’105′
drop table emp
EXEC Recover_Modified_Data_Proc ‘govind’,’dbo.emp’
o/p:-
(0 row(s) affected)
(68 row(s) affected)
(68 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(3 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(4 row(s) affected)
Msg 515, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 433
Cannot insert the value NULL into column ‘FieldValue’, table ‘tempdb.dbo.#temp_Data__________________________________________________________________________________________________________00000000B28F’; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
SELECT [eno],[ename],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([eno],[ename],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
====================================================================================
Thanks Imran, I tried it, but it didn’t give any change :
I did this:
GO
EXEC Recover_Modified_Data_Proc ‘Car’,’CarInfo’
‘Car’ is the name of the Database, ‘CarInfo’ is the name of the table which i updated,
it is very important to me to work: it is more than 39835 rows in one table,
It gives this output, but nochange
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
but data hasn’t changed
Hi Imran,
Great work…
Is it possible to recover the dropped tables also?
Thank you,
Ravi.
Hi Ravi,
Thank you for your feedback.Yes, it is possible to recover the dropped tables because sql server also log this transaction.
Imran
Then let me start expanding your SP to recover dropped tables also.
Thank you,
Ravi.
Good morning… I\’m a student and found this wonderful article. But I want to know how does it work, but the link \”How does it work is explained in Article -2\” doesn\’t work. Please may you tell me where can I read it?
Thanks a lot for your great effort.
Hi
I make your Table and run other script to insert and modify data and create procedure to. But when I execute it, I didn’t get any effect ,just this message :
0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
SELECT [Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
Any data recover !!!
My sql server version is 10.0.53
Hi
I make your Table and run other script to insert and modify data and create procedure to. But when I execute it, I didn\’t get any effect ,just this message :
0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
SELECT [Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
Any data recover !!!
My sql server version is 10.0.53
Hi Muhammad
Have you tried this in MSSQL 2012
Garth
Hi Sir, it is taking too much time when i execute this for one row it is taking 10 minutes around to give the result but i have 800+ rows in db so how much time it will take Please confirm me?
Hi vidit,
Can you please check the sample on your machine.It should not take more than 3 minutes. Also please post the sql server details and your table structure.
Thank you.
Imran
Hi Imran,
This is Great work done and thanks for share your valuable script.
for the updated recoreds working fine in SQL 2008 setup.
we cant recovery data once server or sql service restart..
thanks
ananda
Hi Ananda,
You are right, you cannot recover the data either you restart the server or drop the object.
Thanks,
Imran
hi Imran
so grateful that i found your website. really in trouble now as i overwrite the data in the database. i run your code but i got this error, would you mind helping me, please,
really appreciate your help
here the error message:
(30 row(s) affected)
Msg 515, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 433
Cannot insert the value NULL into column ‘FieldName’, table ‘tempdb.dbo.#temp_Data__________________________________________________________________________________________________________000000000014’; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
SELECT [ID],[UserID],[PackageID],[BalanceEvents],[BalanceEmails],[BalanceSMS],[BalanceSurvey],[IsDeleted],[DateUpdated],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([ID],[UserID],[PackageID],[BalanceEvents],[BalanceEmails],[BalanceSMS],[BalanceSurvey],[IsDeleted],[DateUpdated],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
Hi,
Please post your Sql server details including collation. Also post your table structure.
Thanks
Imran
hi, i’m quite new wth sql server, will this details help?
my sql version: Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86) Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.1 (Build 7601: Service Pack 1)
my server’s collation setting: SQL_Latin1_General_CP1_CI_AS
and here my table structure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EUserPackage](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[UserID] [bigint] NOT NULL,
[PackageID] [tinyint] NOT NULL,
[BalanceEvents] [int] NOT NULL,
[BalanceEmails] [int] NOT NULL,
[BalanceSMS] [int] NOT NULL,
[BalanceSurvey] [int] NOT NULL,
[IsDeleted] [bit] NOT NULL,
[DateUpdated] [smalldatetime] NOT NULL,
CONSTRAINT [PK_EUserPackage] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EUserPackage] ADD CONSTRAINT [DF_EUserPackage_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO
ALTER TABLE [dbo].[EUserPackage] ADD CONSTRAINT [DF_EUserPackage_DateUpdated] DEFAULT (getdate()) FOR [DateUpdated]
GO
INSERT INTO [Eve].[dbo].[EUserPackage]
([UserID]
,[PackageID]
,[BalanceEvents]
,[BalanceEmails]
,[BalanceSMS]
,[BalanceSurvey]
,[IsDeleted]
,[DateUpdated])
VALUES
(60
,1
,1
,50
,0
,0
,0
,2012-01-02)
GO
INSERT INTO [Eve].[dbo].[EUserPackage]
([UserID]
,[PackageID]
,[BalanceEvents]
,[BalanceEmails]
,[BalanceSMS]
,[BalanceSurvey]
,[IsDeleted]
,[DateUpdated])
VALUES
(61
,1
,3
,30
,1
,1
,1
,2012-01-02)
GO
thanks for help
Hi ,
I tried this example in the same environment and it is working fine.May be this is the issue with the data.
Imran
[…] « How to recover modified records from SQL Server without Backup Split Function in SQL Server […]
Hi Imran
i have lost my data through updated query.
I tried same example which you have given but giving me below error
i dont no whats a error
give me feed back
with regrads
Manish Thakor
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
SELECT [Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘(‘.
Hi Manish,
Kindly check your database compatibility level. It should be sql server 2005 and above.
Imran
Hello Imran
It’s really a nice script. But we get problem when we restart the server. Script can’t retrieve any data from transaction log. on a reply of “ananda” on 4th august you have said that we don’t get any data if “restart the server or drop the object”. Only for this reason we can’t use this script properly. Because we want to get all the transaction history at the end of the day.But if the server restarted middle of the day , we can’t retrieve the previous history of restarted time.May i know what is the main straggle point, why we can’t retrieve the data “if server restarted” or have any way out to solve the problem?
Hi Hossain,
Good point.
Let me explain it. Basically once you restart the server, it follows a recovery process, and checks whether each and every transaction is moved from cache to disk or not. Then it removes all the inactive (Committed) transactions from the log and leaves the uncommitted transactions that are under process. Once the recovery process is completed, it will reset the log to the initial state.
Now the question is – what is the remedy for it ? Make sure to take the transaction backup before you restart the server (in case you need to roll back any committed transaction).
And Also, you need to customize this script and replace fn_dblog to fn_dump_dblog and give the path of the transaction log along with additional parameters. It will restore in the same way.
Cheers
Imran
Hi Muhammad,
I tested the script, it works fine if I execute the Update as plain script, but when it is in a stored procedure, I can’t recover any data. Is there anything I can add/change to make it work with stored procedures?
Thanks
Hi Felix,
Definitely, we can do some customization that can automatically restore table into the original state. But it is a little bit risky because lets say if you have modified 50 entries in a day and once you will execute this procedure it will rollback all 50 transactions. Due to this I gave the update statement in the last column,So as per the requirement it can be utilized.
The short cut for this customization is we need to goto line # 638 – 641. this is a query having “Update statement” column, we need to get this query result in a temp table and then we can loop this table and execute all update statements.
Let me know if you need further help
cheers
Imran
Please disregard my initial question, I tested it again, it isn’t the stored procedure. The script can bring back the row if it was changed in the last 5 mins only. I think it is my database setting that does not keep log data for long time. I’ll check and let you know.
Hi Imran,
There’s a nice job you have done here. Right now I’m working with this script and have got some concerns. I would be really grateful if you could help me with these.
1. AT the 136 line of your sp you have this checking(AND [Transaction Name]=’UPDATE’). But when I select * from fn_dblog it most of the gives NULL value.So, I wasn’t getting any results. So, I omitted this line and the code gives desired results. FYI, I am using sql server 2012.
2. Also at line 137 you have checked if [Begin Time] is between our specified time. Here also when I see fn_dblog I find the NULL value for the Begin Time column. So, if you could please check it.
3. I want to get the time when each transaction(in this case, update) took place. Is that somehow possible from reading transaction log?
Thanks in advance.
Hi Kamal,
Excellent points, will work on it, Can you please give me the SQL Server version & collation details.
As far as point 3 concern, yes you can get the time also, Please check the [Begin Time] column in the fn_dblog function.
Please let me know if you need further details.
Thank you.
Imran
Hello again Imran,
Sorry for the late reply.
1. My current working versions are-
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Analysis Services Client Tools 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 4.0.30319.237
Operating System 6.1.7601
And database Collation is SQL_Latin1_General_CP1_CI_AS
2. I checked the [Begin Time] column in the fn_dblog function but it showed NULL for ‘UPDATE’ rows. Please if you could look into this, it would be a great help for me.
Thanks.
Hi Kamal,
If you filter the ‘Update’ rows the [begin time] will be NULL. What you need to do, get the [transaction ID] from the updated rows and then put this transaction ID in fn_dblog.
Then one row u will c with [begin time] as well.
Thank you.
Imran
you are a life saver!
Dear Imran ,i m getting this error for any table
SELECT [invnumber],[Dates],[Customer],[Salesman],[LocalAmount],[Description],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([invnumber],[Dates],[Customer],[Salesman],[LocalAmount],[Description],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘(‘.
Hi Javed,
Please check your sql server compatibility . It is less than 90 (Sql server 2005).
Imran
Its working… Thanks Brother
Hi, noob question: I used the same setup as you and I’ve got this error:
Msg 2714, Level 16, State 3, Procedure Recover_Modified_Data_Proc, Line 643
There is already an object named ‘Recover_Modified_Data_Proc’ in the database.
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
I don’t undersatnd most of the code you’re using, I’m rather new to SQL and wanted to ask you if I need to change something to work for my database and tables.
I’m just exploring the code before trying to fix the data I lost the other day.
Thanks for listening.
Hi Jaun,
Thank you for your feedback. Your result shows that you don’t have the relevant data in your log. Please check you database recovery model. It should be “FULL”.
I wrote the explanation of this source code here. (https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-2/)
Please let me know if you need further explanation.
Imran
Hello Imran,
Can you please give a solution for text datatype also
Thanks,
Shoaib
Hello Imran,
Can I do this without being an admin???
getting some error like
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 70
I tried to do this thing in a web server not as admin..
Hi Samapan,
Thank you for your feedback. Your account must have the rights to execute system level functions.
Imran
And of course a great thanks Imran
Can you make a favor who lost his/her Store proc??
Hi Paul,
Here is the article to recover the stored procedure.
https://raresql.com/2012/12/04/sql-server-recover-the-dropped-objects-view-stored-procedure-function-trigger/
Imran
Hello Imran,
Such a nice script. I’m currently using your script in one of my development projects. Now I’m facing some concerns and need your kind assistance.
I have a table which I need to track that which rows are modified. I’m using your script and it works fine except whenever I try to update a value which was previously NULL. In this case it gives unexpected results.
Say, I have a personal info table which has the following values–
Name: Raju, Work: Project Manager, Phone: 9876, DateofBirth = NULL, RoadNo = NULL(int), BloodGroup = NULL
And I updated the row by setting the DateofBirth to ’25 Aug 1984′ and I run this script and gives the following error at line 433
‘You cannot add value to a datetime column’
Then I debugged the script and found the values are
Name: Raju, Work: Project Manager, Phone: 9876, DateofBirth = Got an error(I think because of assigning different value other than datetime), RoadNo = -8764234, BloodGroup = Raju
FYI, I am using sql server 2012.
So, is there anything I need to change in the script to get the desired results?
Your quick response is highly appreciated.
Thanks in advance.
Hi Raju,
Thank you for your feed back. I fixed this issue. The issue was, that we did not allow NULL in the column [FieldValue] (Line # 423).
Now, I allowed [FieldValue] as NULL in the table [#temp_Data] .
Imran
Hi Imran!
I wonder is it possible to recover dropped and created table by this script? The only tool that I found on the internet was apx sql recovery, but it was too expansive. I would be very grateful for any answer.
Hi Ani,
Thank you for your feedback.
Till now, I could only recover dropped Stored procedure, Functions, triggers & views Only.
(https://raresql.com/2012/12/04/sql-server-recover-the-dropped-objects-view-stored-procedure-function-trigger/)
I will work on it and let you know, mean while you can take a FULL & Transaction backup also.
Please mention your SQL server version.
Imran
Dear Imran,
I am very grateful for your answer, you script helped a lot!!!
I have used “DROP AND CREATE” script for table. SQL server version is 2008. I have only transaction log, and need to recover data from dropped table.
For the modify columns restore stored procedure named [Recover_Modified_Data_Proc]. I understand the collection of the first column (@TotalFixedLengthData) of this part of the query shown below, but not the second (@VariableLength_Offset_Start):
SELECT TOP 1 @TotalFixedLengthData=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))
,@VariableLength_Offset_Start=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))+5+CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2)))) + 1, 2))))/8.0))
FROM @ModifiedRawData
ORDER BY [ID] DESC
Can you explain to me the mechanics of collecting the second column? It is very confusing to me what this query is doing and I need to properly understand it.
Oh, I see now. You are dealing with the uncompressed data structure:
typedef struct UncompressedDataRow {
unsigned char *TagA; // Status Bits A; size = 1 byte {0=version,1-3=record type,4=has null bitmap,5=has variable length columns,6=has version info,7=unused}
unsigned char *TagB; // Status Bits B; size = 1 byte {0=ghost forwarded record,1-7=unused}
unsigned short *Fsize; // Fixed-length size; size = 2 bytes {number of bytes in the fixed length portion}
unsigned char *Fdata; // Fixed-length data; size = (Fsize – 4) bytes {array holding locations of the ENDS of the fixed length fields}
unsigned short *Ncol; // Number of columns; size = 2 bytes {number of fixed length columns}
unsigned char *Nullbits; // NULL bitmap (1 bit for each column in the table; a 1 indicates that the corresponding column is NULL or that the bit is unused.); size = Ceil(Ncol / 8)
unsigned short *VarCount; // Number of variable-length columns stored in row; size = 2 bytes
unsigned short *VarEnds; // Variable column offset array; size = 2 * VarCount
unsigned char *VarData; // Variable-length data; size = VarOff[VarCount] – (Fsize + 4 + Ceiling (Ncol / 8) + 2 * VarCount)
} UncompressedDataRow;
Hi Dann,
Good explanation. You are right.
Thank you.
Imran
its working for current transaction but not for previous transaction session .i mean for dates
Hi Ashwani,
Thank you for your feed back. It all depends upon the data available in SQL server log.
Imran
I am getting “there is insufficient system memory in resource pool ‘internal’ to run this query. sql 2008 r2”
Hi Abdhesh,
Kindly install SQL Server 2008 R2 service pack 1, It will resolve your issue.
Reference : http://support.microsoft.com/kb/982854
Thank you.
Imran
Hello,
The script didn’t return any result when rows are modified through an application. It does return a result when I modify using an update statement in SSMS for example. I checked with ApexSql Log and there are definitely records modified. Any idea why this is?
grtz,
hi,
i used all the scripts mentioned above.It does not returns any row and no errors at all …what to do …?
Hi Rakesh, It recovers the records from the log. I think your recovery model is simple. Due to this you don’t have records in the log . Please try the procedure without date parameter.
hi,
I tried this EXEC Recover_Modified_Data_Proc ‘test’,’dbo.Student’,
with my db name . or do I need to make any change in the procedure…
thanks for your reply…
Hi rakesh,
You should change not only db name but also table name.
Secondly, please check your recovery model type. It must be Full.
You can also use this method to do your recovery.
https://raresql.com/2012/10/10/how-to-recover-the-deleted-records-from-sql-server/
Thanks,
Imran
Thanks for your help.I got the out put when created a new db and table with names test and student respectively as you said.My actual db name contains special characters like dot,underscore etc…is this cause any issue? and i changed Recovery model to “Full”.Now I am getting an output as
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Welcome, I am not sure but may be it is because of . Or _. I will check and update you shortly.
Hi Muhammad,
I tried your example, I got two rows results, but every field in the result is null, the update statement column is empty. Why is that?
Thanks,
Ryan
Hi Rayan,
If you concatenate NULL with any string the result is NULL. May be this is the reason.
Can you please give me your sample query with some sample data, So that I can fix it.
Thank you.
Imran
Hi,
i have got this message. Is it recovered now?????
Msg 2714, Level 16, State 3, Procedure Recover_Modified_Data_Proc, Line 643
There is already an object named ‘Recover_Modified_Data_Proc’ in the database.
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Hi Rave,
Unfortunately, it means that you donot have the records in the log, and it could not recover.
Imran
SELECT*
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN (‘LOP_DELETE_ROWS’)
When i execute the above command it shows list of deleted row….
please help me
thanks
Hi Rave,
But you need to recover modified records not deleted ?
Thank you
Imran
SELECT [RowLog Contents 0]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitName = ‘dbo.TITEMTAG’
AND Context IN ( ‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’ )
AND Operation in ( ‘LOP_DELETE_ROWS’ )
when i execute this command it is showing 4 rows…
Hi Rave,
May be it is some other records that was deleted before. Please run the given below script, If you get the records from given below script than you can recover your modified records.
SELECT [RowLog Contents 0] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = ‘dbo.TITEMTAG’ AND Operation in (‘LOP_MODIFY_ROW’,’LOP_MODIFY_COLUMNS’) And [Context] IN ( ‘LCX_HEAP’,’LCX_CLUSTERED’)
Imran
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
it is showing above error…
thanks
imran
Please put the single quote again in select statement, it should be with red color not grey color.
i want to recover deleted row
Hi Rave,
If you need to recover deletd data, than you should use this stored procedure. https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
Msg 3701, Level 11, State 5, Line 9
Cannot drop the procedure ‘Recover_Deleted_Data_Proc’, because it does not exist or you do not have permission.
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 28
Could not found the table in the defined database
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 28
Could not found the table in the defined database
i executed the code from deleted link that u gave but it is showing above error
thanks
Please enter the correct database name and table name in the stored procedure.
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
It is showing above message….
i do not under stand what it is saying…please explain
please send me a command to check how many row as been deleted.
thanks
As per the error message, you donot have deleted data records in the log. Given below is the script to find the delete data in the log.
Select * from fn_dblog(NULL,NULL) WHERE object_id=object_ID(” + @SchemaName_n_TableName + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in ( ‘LOP_DELETE_ROWS’) And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
Imran
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘”’.
it is showing error message. when i execute the command that u gave now…
thanks
Imran
Please replace the single quotes.
SELECT*
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN (‘LOP_DELETE_ROWS’)
when i execute this command it is showing 2423 row is been deleted…;
i am confused what to do…
thanks
Imran
Hi Rave,
These records belong to multiple tables. You need to filter your records with the schema and table name. Given below is the script.
SELECT* 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 Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in ( ‘LOP_DELETE_ROWS’) And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
sorry i do not get which single quotes.
Hello Imran,
i couldnt debug the the error that i am getting. please help me
Select * from fn_dblog(NULL,NULL) WHERE object_id=object_ID(” + @SchemaName_n_TableName + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in ( ‘LOP_DELETE_ROWS’) And SUBSTRING([RowLog Contents 0], 1, 1)In (0×10,0×30,0×70)
error
Select * from fn_dblog(NULL,NULL) WHERE object_id=object_ID(” + @SchemaName_n_TableName + ”))
AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’) AND Operation in ( ‘LOP_DELETE_ROWS’) And SUBSTRING([RowLog Contents 0], 1, 1)In (0×10,0×30,0×70)
SELECT [RowLog Contents 0]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitName = ‘dbo.TableName’
AND Context IN ( ‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’ )
AND Operation in ( ‘LOP_DELETE_ROWS’ )
;
I Executed the above code. it showed 741 row.
then i executed the complete code to recovery the deleted row. i am getting error
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
Please save this 741 records in a temporary table. Like
SELECT [RowLog Contents 0] into [A] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = dbo.TableName AND Context IN ( LCX_MARK_AS_GHOST, LCX_HEAP ) AND Operation in ( LOP_DELETE_ROWS ) ; Please send me one record from it for testing purpose, the table structure and the collation details.
Imran
SELECT [RowLog Contents 0]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitName = ‘dbo.TableName’
AND Context IN ( ‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’ )
AND Operation in ( ‘LOP_DELETE_ROWS’ )
;
I Executed the above code. it showed 741 row.
then i executed the complete code to recovery the deleted row. i am getting error
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
(8 row(s) affected)
(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 180
There is no data in the log as per the search criteria
Hello Sir ,
i got the following error msg when i trying to execute the proc .
(0 row(s) affected)
(1791 row(s) affected)
(1791 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 536, Level 16, State 5, Procedure Recover_Modified_Data_Proc1, Line 268
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(8 row(s) affected)
(1 row(s) affected)
Msg 536, Level 16, State 5, Procedure Recover_Modified_Data_Proc1, Line 438
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
SELECT [Stu_ID],[Stu_Regist_No],[Stu_Date_of_Admis],[Stu_Class],[Stu_Sec],[Stu_Rollno],[Stu_First_Name],[Stu_Mid_Name],[Stu_Last_Name],[Stu_Date_of_Birth],[Stu_Gender],[KinsRelation],[KinsName],[Stu_Address],[Stu_City],[Stu_State],[Stu_Country],[Stu_Phone],[stu_Phone1],[Stu_Email],[Stu_Reg_Fee],[Stu_Refund_Sec],[Stu_Building_Fund],[Stu_Lab_Fee],[Stu_Tution_Fee],[Stu_Library_Fee],[Other_Fee],[Total_Fee],[Paid_Amount],[Balance_amount],[Stu_Type],[Mode_of_coming],[Mod_Fee],[Session_ID],[Stu_Photo],[FeeDate],[ReciptNo],[AdmissionNo],[Concession],[RemarksCon],[Transport_Charges],[BRid],[AdmnNo],[TelNO],[UseTransport],[TransportArea],[UseMilk],[UseFood],[SchoolLeft],[LeavingDate],[Reason],[TcIssued],[CertificateNo],[OldStudent],[StuJoinClass],[StuJoinSec],[sType],[ReasonDOB],[JoiningDate],[LeavingClass],[LeavingSection],[Nationality],[Games],[FreeStudent],[StruckOFf],[Admn_Type],[Admn_No],[VehicleID],[VehicleID1],[NationalityID],[Stu_GroupId],[stucatid],[stationid],[Fee],[GroupID],[Stu_OtherName],[GameID],[ExtraActID],[Adharid],[FatherPanNo],[MotherPanNo],[RTEorNot],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([Stu_ID],[Stu_Regist_No],[Stu_Date_of_Admis],[Stu_Class],[Stu_Sec],[Stu_Rollno],[Stu_First_Name],[Stu_Mid_Name],[Stu_Last_Name],[Stu_Date_of_Birth],[Stu_Gender],[KinsRelation],[KinsName],[Stu_Address],[Stu_City],[Stu_State],[Stu_Country],[Stu_Phone],[stu_Phone1],[Stu_Email],[Stu_Reg_Fee],[Stu_Refund_Sec],[Stu_Building_Fund],[Stu_Lab_Fee],[Stu_Tution_Fee],[Stu_Library_Fee],[Other_Fee],[Total_Fee],[Paid_Amount],[Balance_amount],[Stu_Type],[Mode_of_coming],[Mod_Fee],[Session_ID],[Stu_Photo],[FeeDate],[ReciptNo],[AdmissionNo],[Concession],[RemarksCon],[Transport_Charges],[BRid],[AdmnNo],[TelNO],[UseTransport],[TransportArea],[UseMilk],[UseFood],[SchoolLeft],[LeavingDate],[Reason],[TcIssued],[CertificateNo],[OldStudent],[StuJoinClass],[StuJoinSec],[sType],[ReasonDOB],[JoiningDate],[LeavingClass],[LeavingSection],[Nationality],[Games],[FreeStudent],[StruckOFf],[Admn_Type],[Admn_No],[VehicleID],[VehicleID1],[NationalityID],[Stu_GroupId],[stucatid],[stationid],[Fee],[GroupID],[Stu_OtherName],[GameID],[ExtraActID],[Adharid],[FatherPanNo],[MotherPanNo],[RTEorNot],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
Thanks,
Deepak
Hi Deepak,
This recovery script recovers only limited number of datatypes and I think your tables has some of the datatypes that is not covered.
Thank you.
Imran
Hello Sir ,
Thanks for your response .yes i think we are using image data type thats why it shows this kind of error . anyways thanks for sharing such nice work.
Thanks ,
Deepak
Hi Imran,
The SP is running fine. but in my table i m having a DOB as date column. while parsing that i m getting an error “Conversion failed when converting date and/or time from character string.” in line WHEN system_type_id IN( 40) Then ….
Please help me to proceed further
Hi Muhammad,
I also get:
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 266
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 433
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
The first error 5 times scattered in between successful executed statements.
I’m using:
Windows Server 2008 R2 SP1 X64
SQL Server 10.0.5500.0 SP3 Standard Edition (64-bit)
The data types in my table:
binary, datetime, float, int, text, tinyint, varchar
I guess the TEXT data type is causing the errors since it is not in your list?
Hi,
You are right, it is because of text data type.
Thanks
Imran
Hi Muhammad,
I have the same error, but my data types are jus int, tinyint and varchar,
(9 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 362
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Versión SQL:
Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
any ideas?
Hi Daniel,
I did not test it on SQL Server 2012, can you please provide me a sample table and few update queries.
Thanks
Imran
HI Muhammad,
I can not create your Stored procedure and Errors say .
sg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 82
Line 82: Incorrect syntax near ‘.’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 83
Incorrect syntax near the keyword ‘AS’.
Msg 197, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 87
EXECUTE cannot be used as a source when inserting into a table variable.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 117
Line 117: Incorrect syntax near ‘XML’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 131
Incorrect syntax near the keyword ‘AND’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 140
Incorrect syntax near the keyword ‘GROUP’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 145
Line 145: Incorrect syntax near ‘.’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 184
Line 184: Incorrect syntax near ‘.’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 188
Line 188: Incorrect syntax near ‘.’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 199
Line 199: Incorrect syntax near ‘.’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 200
Line 200: Incorrect syntax near ‘.’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 207
Line 207: Incorrect syntax near ‘.’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 208
Incorrect syntax near the keyword ‘AS’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 326
Incorrect syntax near the keyword ‘WITH’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 389
Line 389: Incorrect syntax near ‘,’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 392
Line 392: Incorrect syntax near ‘,’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 393
Line 393: Incorrect syntax near ‘,’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 394
Line 394: Incorrect syntax near ‘,’.
Msg 195, Level 15, State 10, Procedure Recover_Modified_Data_Proc, Line 395
‘ROW_NUMBER’ is not a recognized function name.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 416
Incorrect syntax near the keyword ‘FOR’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 433
Incorrect syntax near the keyword ‘With’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 443
Line 443: Incorrect syntax near ‘.8000’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 487
Line 487: Incorrect syntax near ‘.8000’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 493
Line 493: Incorrect syntax near ‘_length’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 500
Line 500: Incorrect syntax near ‘_length’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 535
Line 535: Incorrect syntax near ‘.’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 598
Line 598: Incorrect syntax near ‘,’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 600
Line 600: Incorrect syntax near ‘XML’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 603
Incorrect syntax near the keyword ‘FOR’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 608
Incorrect syntax near the keyword ‘FOR’.
Msg 156, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 611
Incorrect syntax near the keyword ‘FOR’.
Msg 170, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 633
Line 633: Incorrect syntax near ‘XML’.
May I know how to create this procedure ??
I hope you will reply 😦
Hi,
What is your SQL Server version ?
Imran
[…] publishing a solution how to recover the modified / updated records without backup in one of my previous articles, I received lot of queries inquiring how to find who modified / […]
Hi, Muhammad Imran, do you have any suggestion for sql server 2012? Can i help me?
Hi Adrian,
Currently, I am working on SQL Server 2012 recovery modules. Will make this script compatible with SQL Server 2012 shortly.
Thanks,
Imran
I am trying to experiment with some edge cases.
Using this script:
USE master
GO
DROP DATABASE oddball
go
CREATE DATABASE oddball
GO
USE oddball
GO
CREATE TABLE rowoverflowtest (c1 INT, c2 VARCHAR (8000), c3 VARCHAR (8000));
GO
CREATE CLUSTERED INDEX row_cl ON rowoverflowtest (c1);
GO
INSERT INTO rowoverflowtest VALUES (1, REPLICATE (‘a’, 100), REPLICATE (‘b’, 100));
GO
INSERT INTO rowoverflowtest VALUES (2, REPLICATE (‘a’, 100), REPLICATE (‘b’, 100));
GO
INSERT INTO rowoverflowtest VALUES (3, REPLICATE (‘a’, 100), REPLICATE (‘b’, 100));
GO
INSERT INTO rowoverflowtest VALUES (4, REPLICATE (‘a’, 100), REPLICATE (‘b’, 100));
GO
INSERT INTO rowoverflowtest VALUES (5, REPLICATE (‘a’, 100), REPLICATE (‘b’, 100));
GO
UPDATE rowoverflowtest SET c3 = REPLICATE (‘c’, 8000) WHERE c1 = 3;
GO
CREATE PROCEDURE [dbo].[Recover_Modified_Data_Proc] @Database_Name NVARCHAR(MAX)
,@SchemaName_n_TableName NVARCHAR(MAX)
,@Date_From DATETIME = ‘1900/01/01’
,@Date_To DATETIME = ‘9999/12/31’
AS
DECLARE @parms NVARCHAR(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @RowLogContents0 VARBINARY(8000)
DECLARE @RowLogContents1 VARBINARY(8000)
DECLARE @RowLogContents3 VARBINARY(8000)
DECLARE @RowLogContents3_Var VARCHAR(MAX)
DECLARE @RowLogContents4 VARBINARY(8000)
DECLARE @LogRecord VARBINARY(8000)
DECLARE @LogRecord_Var VARCHAR(MAX)
DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @AllocUnitID BIGINT
DECLARE @TransactionID VARCHAR(MAX)
DECLARE @TrID SYSNAME
DECLARE @Operation VARCHAR(MAX)
DECLARE @DatabaseCollation VARCHAR(MAX)
/* Pick The actual data
*/
DECLARE @temppagedata TABLE (
[ParentObject] SYSNAME
,[Object] SYSNAME
,[Field] SYSNAME
,[Value] SYSNAME
)
DECLARE @pagedata TABLE (
[Page ID] SYSNAME
,[AllocUnitId] BIGINT
,[Tr ID] SYSNAME
,[ParentObject] SYSNAME
,[Object] SYSNAME
,[Field] SYSNAME
,[Value] SYSNAME
)
DECLARE Page_Data_Cursor CURSOR
FOR
/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
SELECT [PAGE ID]
,[Slot ID]
,[AllocUnitId]
,[Transaction ID]
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’
)
/*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
)
/****************************************/
GROUP BY [PAGE ID]
,[Slot ID]
,[AllocUnitId]
,[Transaction ID]
ORDER BY [Slot ID]
OPEN Page_Data_Cursor
FETCH NEXT
FROM Page_Data_Cursor
INTO @ConsolidatedPageID
,@Slotid
,@AllocUnitID
,@TrID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @hex_pageid AS VARCHAR(Max)
/*Page ID contains File Number and page number It looks like 0001:00000130.
In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/
SET @Fileid = SUBSTRING(@ConsolidatedPageID, 0, CHARINDEX(‘:’, @ConsolidatedPageID)) — Seperate File ID from Page ID
SET @hex_pageid = ‘0x’ + SUBSTRING(@ConsolidatedPageID, CHARINDEX(‘:’, @ConsolidatedPageID) + 1, Len(@ConsolidatedPageID)) —Seperate the page ID
SELECT @Pageid = Convert(INT, cast(” AS XML).value(‘xs:hexBinary(substring(sql:variable(“@hex_pageid”),sql:column(“t.pos”)) )’, ‘varbinary(max)’)) — Convert Page ID from hex to integer
FROM (
SELECT CASE substring(@hex_pageid, 1, 2)
WHEN ‘0x’
THEN 3
ELSE 0
END
) AS t(pos)
DELETE @temppagedata
— Now we need to get the actual data (After modification) from the page
INSERT INTO @temppagedata
EXEC (‘DBCC PAGE(‘ + @DataBase_Name + ‘, ‘ + @fileid + ‘, ‘ + @pageid + ‘, 3) with tableresults,no_infomsgs;’);
— Add Page Number and allocUnit ID in data to identity which one page it belongs to.
INSERT INTO @pagedata
SELECT @ConsolidatedPageID
,@AllocUnitID
,@TrID
,[ParentObject]
,[Object]
,[Field]
,[Value]
FROM @temppagedata
FETCH NEXT
FROM Page_Data_Cursor
INTO @ConsolidatedPageID
,@Slotid
,@AllocUnitID
,@TrID
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);
DECLARE @ModifiedRawData TABLE (
[ID] INT IDENTITY(1, 1)
,[PAGE ID] VARCHAR(MAX)
,[Slot ID] INT
,[AllocUnitId] BIGINT
,[Tr ID] SYSNAME
,[RowLog Contents 0_var] VARCHAR(MAX)
,[RowLog Contents 0] VARBINARY(8000)
)
–The modified data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
–This hex value is in string format
INSERT INTO @ModifiedRawData (
[PAGE ID]
,[Slot ID]
,[AllocUnitId]
,[Tr ID]
,[RowLog Contents 0_var]
)
SELECT B.[PAGE ID]
,A.[Slot ID]
,A.[AllocUnitId]
,A.[Transaction ID]
,(
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%’
GROUP BY [Value]
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 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
)
/****************************************/
GROUP BY B.[PAGE ID]
,A.[Slot ID]
,A.[AllocUnitId]
,A.[Transaction ID]
ORDER BY [Slot ID]
— Convert the hex value data in string, convert it into Hex value as well.
UPDATE @ModifiedRawData
SET [RowLog Contents 0] = cast(” AS XML).value(‘xs:hexBinary(substring(sql:column(“[RowLog Contents 0_var]”), 0) )’, ‘varbinary(max)’)
FROM @ModifiedRawData
—Now we have modifed data plus its slot ID , page ID and allocunit as well.
–After that we need to get the old values before modfication, these datas are in chunks.
DECLARE Page_Data_Cursor CURSOR
FOR
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 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
)
/****************************************/
ORDER BY [Slot ID]
,[Transaction ID] DESC
OPEN Page_Data_Cursor
FETCH NEXT
FROM Page_Data_Cursor
INTO @ConsolidatedPageID
,@Slotid
,@AllocUnitID
,@TransactionID
,@RowLogContents0
,@RowLogContents1
,@RowLogContents3
,@RowLogContents4
,@LogRecord
,@Operation
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Operation = ‘LOP_MODIFY_ROW’
BEGIN
/* If it is @Operation Type is ‘LOP_MODIFY_ROW’ then it is very simple to recover the modified data. The old data is in [RowLog Contents 0] Field and modified data is in [RowLog Contents 1] Field. Simply replace it with the modified data and get the old data.
*/
INSERT INTO @ModifiedRawData (
[PAGE ID]
,[Slot ID]
,[AllocUnitId]
,[Tr ID]
,[RowLog Contents 0_var]
)
SELECT TOP 1 @ConsolidatedPageID AS [PAGE ID]
,@Slotid AS [Slot ID]
,@AllocUnitID AS [AllocUnitId]
,@TransactionID AS [Tr ID]
,REPLACE(UPPER([RowLog Contents 0_var]), UPPER(CAST(” AS XML).value(‘xs:hexBinary(sql:variable(“@RowLogContents1″) )’, ‘varchar(max)’)), UPPER(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
— Convert the old data which is in string format to 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
WHERE [Slot ID] = @SlotID
END
IF @Operation = ‘LOP_MODIFY_COLUMNS’
BEGIN
/* If it is @Operation Type is ‘LOP_MODIFY_ROW’ then we need to follow a different procedure to recover modified
.Because this time the data is also in chunks but merge with the data log.
*/
–First, we need to get the [RowLog Contents 3] Because in [Log Record] field the modified data is available after the [RowLog Contents 3] data.
SET @RowLogContents3_Var = cast(” AS XML).value(‘xs:hexBinary(sql:variable(“@RowLogContents3″) )’, ‘varchar(max)’)
SET @LogRecord_Var = cast(” AS XML).value(‘xs:hexBinary(sql:variable(“@LogRecord”))’, ‘varchar(max)’)
DECLARE @RowLogData_Var VARCHAR(Max)
DECLARE @RowLogData_Hex VARBINARY(Max)
—First get the modifed data chunks in string format
SET @RowLogData_Var = SUBSTRING(@LogRecord_Var, CHARINDEX(@RowLogContents3_Var, @LogRecord_Var) + LEN(@RowLogContents3_Var), LEN(@LogRecord_Var))
–Then convert it into the hex values.
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)
DECLARE @TotalFixedLengthData INT
DECLARE @FixedLength_Offset INT
DECLARE @VariableLength_Offset INT
DECLARE @VariableLength_Offset_Start INT
DECLARE @VariableLengthIncrease INT
DECLARE @FixedLengthIncrease INT
DECLARE @OldFixedLengthStartPosition INT
DECLARE @FixedLength_Loc INT
DECLARE @VariableLength_Loc INT
DECLARE @FixedOldValues VARBINARY(MAX)
DECLARE @FixedNewValues VARBINARY(MAX)
DECLARE @VariableOldValues VARBINARY(MAX)
DECLARE @VariableNewValues VARBINARY(MAX)
— Before recovering the modfied data we need to get the total fixed length data size and start position of the varaible data
SELECT TOP 1 @TotalFixedLengthData = CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2))))
,@VariableLength_Offset_Start = CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 5 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0))
FROM @ModifiedRawData
ORDER BY [ID] DESC
SET @FixedLength_Offset = CONVERT(BINARY (2), REVERSE(CONVERT(BINARY (4), (@RowLogContents0)))) –)
SET @VariableLength_Offset = CONVERT(INT, CONVERT(BINARY (2), REVERSE(@RowLogContents0)))
/* We already have modified data chunks in @RowLogData_Hex but this data is in merge format (modified plus actual data)
So , here we need [Row Log Contents 1] field , because in this field we have the data length both the modified and actual data
so this length will help us to break it into original and modified data chunks.
*/
SET @FixedLength_Loc = CONVERT(INT, SUBSTRING(@RowLogContents1, 1, 1))
SET @VariableLength_Loc = CONVERT(INT, SUBSTRING(@RowLogContents1, 3, 1))
/*First , we need to break Fix length data actual with the help of data length */
SET @OldFixedLengthStartPosition = CHARINDEX(@RowLogContents4, @RowLogData_Hex)
SET @FixedOldValues = SUBSTRING(@RowLogData_Hex, @OldFixedLengthStartPosition, @FixedLength_Loc)
SET @FixedLengthIncrease = (
CASE
WHEN (Len(@FixedOldValues) % 4) = 0
THEN 1
ELSE (4 – (LEN(@FixedOldValues) % 4))
END
)
/*After that , we need to break Fix length data modified data with the help of data length */
SET @FixedNewValues = SUBSTRING(@RowLogData_Hex, @OldFixedLengthStartPosition + @FixedLength_Loc + @FixedLengthIncrease, @FixedLength_Loc)
/*Same we need to break the variable data with the help of data length*/
SET @VariableOldValues = SUBSTRING(@RowLogData_Hex, @OldFixedLengthStartPosition + @FixedLength_Loc + @FixedLengthIncrease + @FixedLength_Loc + (@FixedLengthIncrease), @VariableLength_Loc)
SET @VariableLengthIncrease = (
CASE
WHEN (LEN(@VariableOldValues) % 4) = 0
THEN 1
ELSE (4 – (Len(@VariableOldValues) % 4)) + 1
END
)
SET @VariableOldValues = (
CASE
WHEN @VariableLength_Loc = 1
THEN @VariableOldValues + 0x00
ELSE @VariableOldValues
END
)
SET @VariableNewValues = SUBSTRING(SUBSTRING(@RowLogData_Hex, @OldFixedLengthStartPosition + @FixedLength_Loc + @FixedLengthIncrease + @FixedLength_Loc + (@FixedLengthIncrease – 1) + @VariableLength_Loc + @VariableLengthIncrease, Len(@RowLogData_Hex) + 1), 1, Len(@RowLogData_Hex) + 1) –LEN(@VariableOldValues)
/*here we need to replace the fixed length & variable length actaul data with modifed data
*/
SELECT TOP 1 @VariableNewValues = CASE
WHEN CHARINDEX(SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) + 1), [RowLog Contents 0]) 0
THEN SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) + 1)
WHEN CHARINDEX(SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues)), [RowLog Contents 0]) 0
THEN SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues))
WHEN CHARINDEX(SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) – 1), [RowLog Contents 0]) 0
THEN SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) – 1) –3 –Substring(@VariableNewValues,0,Len(@VariableNewValues)-1)
WHEN CHARINDEX(SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) – 2), [RowLog Contents 0]) 0
THEN SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) – 2)
WHEN CHARINDEX(SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) – 3), [RowLog Contents 0]) 0
THEN SUBSTRING(@VariableNewValues, 0, LEN(@VariableNewValues) – 3) –5–Substring(@VariableNewValues,0,Len(@VariableNewValues)-3)
END
FROM @ModifiedRawData
WHERE [Slot ID] = @SlotID
ORDER BY [ID] DESC
INSERT INTO @ModifiedRawData (
[PAGE ID]
,[Slot ID]
,[AllocUnitId]
,[Tr ID]
,[RowLog Contents 0_var]
,[RowLog Contents 0]
)
SELECT TOP 1 @ConsolidatedPageID AS [PAGE ID]
,@Slotid AS [Slot ID]
,@AllocUnitID AS [AllocUnitId]
,@TransactionID
,NULL
,CAST(REPLACE(SUBSTRING([RowLog Contents 0], 0, @TotalFixedLengthData + 1), @FixedNewValues, @FixedOldValues) AS VARBINARY(max)) + SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2) + SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3, CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2)))) / 8.0))) + SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2)))) / 8.0)), 2) + SUBSTRING([RowLog Contents 0], @VariableLength_Offset_Start, (@VariableLength_Offset – (@VariableLength_Offset_Start – 1))) + CAST(REPLACE(SUBSTRING([RowLog Contents 0], @VariableLength_Offset + 1, LEN(@VariableNewValues)), @VariableNewValues, @VariableOldValues) AS VARBINARY) + Substring([RowLog Contents 0], @VariableLength_Offset + LEN(
@VariableNewValues) + 1, LEN([RowLog Contents 0]))
FROM @ModifiedRawData
WHERE [Slot ID] = @SlotID
ORDER BY [ID] DESC
END
FETCH NEXT
FROM Page_Data_Cursor
INTO @ConsolidatedPageID
,@Slotid
,@AllocUnitID
,@TransactionID
,@RowLogContents0
,@RowLogContents1
,@RowLogContents3
,@RowLogContents4
,@LogRecord
,@Operation
END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @RowLogContents VARBINARY(8000)
DECLARE @AllocUnitName NVARCHAR(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @bitTable TABLE (
[ID] INT
,[Bitvalue] INT
)
—-Create table to set the bit position of one byte.
INSERT INTO @bitTable
SELECT 0, 2
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 2, 4
UNION ALL
SELECT 3, 8
UNION ALL
SELECT 4, 16
UNION ALL
SELECT 5, 32
UNION ALL
SELECT 6, 64
UNION ALL
SELECT 7, 128
–Create table to collect the row data.
DECLARE @DeletedRecords TABLE (
[ID] INT IDENTITY(1, 1)
,[RowLogContents] VARBINARY(8000)
,[AllocUnitID] BIGINT
,[Tr ID] SYSNAME
,[Transaction ID] NVARCHAR(Max)
,[Slot ID] INT
,[FixedLengthData] SMALLINT
,[TotalNoOfCols] SMALLINT
,[NullBitMapLength] SMALLINT
,[NullBytes] VARBINARY(8000)
,[TotalNoofVarCols] SMALLINT
,[ColumnOffsetArray] VARBINARY(8000)
,[VarColumnStart] SMALLINT
,[NullBitMap] VARCHAR(MAX)
)
–Create a common table expression to get all the row data plus how many bytes we have for each row.
;
WITH RowData
AS (
SELECT [RowLog Contents 0] AS [RowLogContents]
,@AllocUnitID AS [AllocUnitID]
,[Tr ID]
,[ID] AS [Transaction ID]
,[Slot ID] AS [Slot ID]
–[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
,CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] –@FixedLengthData
–[TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
,CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) AS [TotalNoOfCols]
–[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)) AS [NullBitMapLength]
–[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3, CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0))) AS [NullBytes]
–[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
,(
CASE
WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (
0x30
,0x70
)
THEN CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)), 2))))
ELSE NULL
END
) AS [TotalNoofVarCols]
–[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
,(
CASE
WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x30, 0x70 )
THEN SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)) + 2, (
CASE
WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x30, 0x70 )
THEN CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)), 2))))
ELSE NULL
END
) * 2)
ELSE NULL
END
) AS [ColumnOffsetArray]
— Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
,CASE
WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x30, 0x70 )
THEN (
CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)) + (
(
CASE
WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN ( 0x30, 0x70 )
THEN CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3 + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) / 8.0)), 2))))
ELSE NULL
END
) * 2
)
)
ELSE NULL
END AS [VarColumnStart]
FROM @ModifiedRawData
)
,
—Use this technique to repeate the row till the no of bytes of the row.
N1 (n)
AS (
SELECT 1
UNION ALL
SELECT 1
)
,N2 (n)
AS (
SELECT 1
FROM N1 AS X
,N1 AS Y
)
,N3 (n)
AS (
SELECT 1
FROM N2 AS X
,N2 AS Y
)
,N4 (n)
AS (
SELECT ROW_NUMBER() OVER (
ORDER BY X.n
)
FROM N3 AS X
,N3 AS Y
)
INSERT INTO @DeletedRecords
SELECT RowLogContents
,[AllocUnitID]
,[Tr ID]
,[Transaction ID]
,[Slot ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
–Get the Null value against each column (1 means null zero means not null)
,[NullBitMap] = (
REPLACE(STUFF((
SELECT ‘,’ + (
CASE
WHEN [ID] = 0
THEN CONVERT(NVARCHAR(1), (SUBSTRING(NullBytes, n, 1) % 2))
ELSE CONVERT(NVARCHAR(1), ((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2))
END
) –as [nullBitMap]
FROM N4 AS Nums
INNER JOIN RowData AS C ON n <= NullBitMapLength
CROSS JOIN @bitTable
WHERE C.[RowLogContents] = D.[RowLogContents]
ORDER BY [RowLogContents]
,n ASC
FOR XML PATH('')
), 1, 1, ''), ',', '')
)
FROM RowData D
CREATE TABLE [#temp_Data] (
[FieldName] VARCHAR(MAX) COLLATE database_default NOT NULL
,[FieldValue] VARCHAR(MAX) COLLATE database_default NULL
,[Rowlogcontents] VARBINARY(8000)
,[Transaction ID] VARCHAR(MAX) COLLATE database_default NOT NULL
,[Tr ID] SYSNAME
,[Slot ID] INT
,[NonID] INT
–[System_type_id] int
)
—Create common table expression and join it with the rowdata table
–to get each column details
;
WITH CTE
AS (
/*This part is for variable data columns*/
SELECT A.[ID]
,Rowlogcontents
,[Transaction ID]
,[Tr ID]
,[Slot ID]
,NAME
,cols.leaf_null_bit AS nullbit
,leaf_offset
,ISNULL(syscolumns.length, cols.max_length) AS [length]
,cols.system_type_id
,cols.leaf_bit_position AS bitpos
,ISNULL(syscolumns.xprec, cols.precision) AS xprec
,ISNULL(syscolumns.xscale, cols.scale) AS xscale
,SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null
,
–Calculate the variable column size from the variable column offset array
(
CASE
WHEN leaf_offset < 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0
THEN CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * leaf_offset * – 1) – 1, 2))))
ELSE 0
END
) AS [Column value Size]
,
—Calculate the column length
(
CASE
WHEN leaf_offset < 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0
THEN CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * (leaf_offset * – 1)) – 1, 2)))) – ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * ((leaf_offset * – 1) – 1)) – 1, 2)))), 0), [varColumnStart])
ELSE 0
END
) AS [Column Length]
–Get the Hexa decimal value from the RowlogContent
–HexValue of the variable column=Substring([Column value Size] – [Column Length] + 1,[Column Length])
–This is the data of your column but in the Hexvalue
,CASE
WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1
THEN NULL
ELSE SUBSTRING(Rowlogcontents, (
(
CASE
WHEN leaf_offset < 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0
THEN CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * leaf_offset * – 1) – 1, 2))))
ELSE 0
END
) – (
(
CASE
WHEN leaf_offset < 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0
THEN CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * (leaf_offset * – 1)) – 1, 2)))) – ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * ((leaf_offset * – 1) – 1)) – 1, 2)))), 0), [varColumnStart])
ELSE 0
END
)
)
) + 1, (
(
CASE
WHEN leaf_offset < 1
AND SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 0
THEN CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * (leaf_offset * – 1)) – 1, 2)))) – ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(SUBSTRING([ColumnOffsetArray], (2 * ((leaf_offset * – 1) – 1)) – 1, 2)))), 0), [varColumnStart])
ELSE 0
END
)
))
END AS hex_Value
FROM @DeletedRecords A
INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
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
)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT JOIN syscolumns ON syscolumns.id = partitions.object_id
AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset 1
THEN max_length
ELSE 0
END), 0)
FROM sys.system_internals_partition_columns C
WHERE cols.partition_id = C.partition_id
AND C.leaf_null_bit 1
THEN max_length
ELSE 0
END), 0)
FROM sys.system_internals_partition_columns C
WHERE cols.partition_id = C.partition_id
AND C.leaf_null_bit 0
)
–Converting data from Hexvalue to its orgional datatype.
–Implemented datatype conversion mechanism for each datatype
–Select * from sys.columns Where [object_id]=object_id(” + @SchemaName_n_TableName + ”)
–Select * from CTE
INSERT INTO #temp_Data
SELECT NAME
,CASE
WHEN system_type_id IN (
231
,239
)
THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max), hex_Value))) –NVARCHAR ,NCHAR
WHEN system_type_id IN (
167
,175
)
THEN LTRIM(RTRIM(CONVERT(VARCHAR(max), REPLACE(hex_Value, 0x00, 0x20)))) –VARCHAR,CHAR
WHEN system_type_id = 48
THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY (1), REVERSE(hex_Value)))) –TINY INTEGER
WHEN system_type_id = 52
THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY (2), REVERSE(hex_Value)))) –SMALL INTEGER
WHEN system_type_id = 56
THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY (4), REVERSE(hex_Value)))) — INTEGER
WHEN system_type_id = 127
THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY (8), REVERSE(hex_Value)))) — BIG INTEGER
WHEN system_type_id = 61
THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) –DATETIME
WHEN system_type_id IN (40)
THEN CONVERT(VARCHAR(MAX), CONVERT(DATE, CONVERT(VARBINARY(8000), (hex_Value))), 100) –DATE This datatype only works for SQL Server 2008
WHEN system_type_id = 58
THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLDATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) –SMALL DATETIME
WHEN system_type_id = 108
THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(38, 30), CONVERT(VARBINARY, CONVERT(VARBINARY, xprec) + CONVERT(VARBINARY, xscale)) + CONVERT(VARBINARY(1), 0) + hex_Value) AS FLOAT)) — NUMERIC
WHEN system_type_id IN (
60
,122
)
THEN CONVERT(VARCHAR(MAX), Convert(MONEY, Convert(VARBINARY(8000), Reverse(hex_Value))), 2) –MONEY,SMALLMONEY
WHEN system_type_id = 106
THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(DECIMAL(38, 34), CONVERT(VARBINARY, Convert(VARBINARY, xprec) + CONVERT(VARBINARY, xscale)) + CONVERT(VARBINARY(1), 0) + hex_Value) AS FLOAT)) — DECIMAL
WHEN system_type_id = 104
THEN CONVERT(VARCHAR(MAX), CONVERT(BIT, CONVERT(BINARY (1), hex_Value) % 2)) — BIT
WHEN system_type_id = 62
THEN RTRIM(LTRIM(STR(CONVERT(FLOAT, SIGN(CAST(CONVERT(VARBINARY(8000), Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000), Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), – 52)) * POWER(CAST(2 AS FLOAT), ((CAST(CONVERT(VARBINARY(8000), Reverse(hex_Value)) AS BIGINT) & 0x7FF0000000000000) / EXP(52 * LOG(2)) – 1023))), 53, LEN(hex_Value)))) — FLOAT
WHEN system_type_id = 59
THEN Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000), REVERSE(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000), Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS REAL), – 23)) * POWER(CAST(2 AS REAL), (((CAST(CONVERT(VARBINARY(8000), Reverse(hex_Value)) AS INT)) & 0x7F800000) / EXP(23 * LOG(2)) – 127)) AS REAL), 23, 23)), 8) –Real
WHEN system_type_id IN (
165
,173
)
THEN (
CASE
WHEN CHARINDEX(0x, cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘VARBINARY(8000)’)) = 0
THEN ‘0x’
ELSE ”
END
) + cast(” AS XML).value(‘xs:hexBinary(sql:column(“hex_Value”))’, ‘varchar(max)’) — BINARY,VARBINARY
WHEN system_type_id = 36
THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, hex_Value)) –UNIQUEIDENTIFIER
END AS FieldValue
,[Rowlogcontents]
,[Transaction ID]
,[Tr ID]
,[Slot ID]
,[ID]
FROM CTE
ORDER BY nullbit
/*Create Update statement*/
/*Now we have the modified and actual data as well*/
/*We need to create the update statement in case of recovery*/
;
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
,40
,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]
,A.[tr ID]
,’D’ AS [Type]
,[A].Rowlogcontents
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName] = [B].[FieldName]
AND [A].[Slot ID] = [B].[Slot ID]
–And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID] = (
SELECT Min(Cast([Transaction ID] AS INT)) AS [Transaction ID]
FROM #temp_Data 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
,40
,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]
,A.[tr ID]
,’S’ AS [Type]
,[A].Rowlogcontents
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName] = [B].[FieldName]
AND [A].[Slot ID] = [B].[Slot ID]
–And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID] = (
SELECT Min(Cast([Transaction ID] AS INT)) AS [Transaction ID]
FROM #temp_Data 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 #temp_Data 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, ”)) – 2) + ‘ 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, ”)) – 4) AS [Update Statement]
,[Slot ID]
,[Transaction ID]
,A.[tr ID]
,Rowlogcontents
,[A].[NonID]
FROM CTE A
GROUP BY [Slot ID]
,[Transaction ID]
,A.[tr ID]
,Rowlogcontents
,[A].[NonID]
)
INSERT INTO #temp_Data
SELECT ‘Update Statement’
,ISNULL(A.[Update Statement], ”)
,A.[Rowlogcontents]
,A.[Transaction ID]
,A.[tr ID]
,A.[Slot ID]
,A.[NonID]
FROM CTEUpdateQuery A
INSERT INTO #temp_Data
SELECT ‘Modified User Name’
,C.[name]
,A.[RowLogcontents]
,A.[Transaction ID]
,A.[Tr ID]
,A.[Slot ID]
,A.[NonID]
FROM #temp_Data A
INNER JOIN fn_dblog(NULL, NULL) B ON A.[tr ID] = B.[Transaction ID]
AND B.[Operation] = ‘LOP_BEGIN_XACT’
AND B.[Context] = ‘LCX_NULL’
AND B.[Transaction Name] = ‘UPDATE’
INNER JOIN sys.sysusers C ON B.[Transaction SID] = C.[Sid]
WHERE [FieldName] = ‘Update Statement’
GROUP BY C.[name]
,A.[RowLogcontents]
,A.[Transaction ID]
,A.[Tr ID]
,A.[Slot ID]
,A.[NonID]
UNION ALL
SELECT ‘Modified Date Time’
,B.[Begin Time]
,A.[RowLogcontents]
,A.[Transaction ID]
,A.[Tr ID]
,A.[Slot ID]
,A.[NonID]
FROM #temp_Data A
INNER JOIN fn_dblog(NULL, NULL) B ON A.[tr ID] = B.[Transaction ID]
AND B.[Operation] = ‘LOP_BEGIN_XACT’
AND B.[Context] = ‘LCX_NULL’
AND B.[Transaction Name] = ‘UPDATE’
WHERE [FieldName] = ‘Update Statement’
GROUP BY B.[Begin Time]
,A.[RowLogcontents]
,A.[Transaction ID]
,A.[Tr ID]
,A.[Slot ID]
,A.[NonID]
/**************************/
–Create the column name in the same order to do pivot table.
DECLARE @FieldName VARCHAR(max)
SET @FieldName = STUFF((
SELECT ‘,’ + CAST(QUOTENAME([Name]) AS VARCHAR(MAX))
FROM syscolumns
WHERE id = object_id(” + @SchemaName_n_TableName + ”)
FOR XML PATH(”)
), 1, 1, ”)
PRINT @FieldName
–Finally did pivot table and got the data back in the same format.
–The [Update Statement] column will give you the query that you can execute in case of recovery.
SET @sql = ‘SELECT ‘ + @FieldName + ‘,[Update Statement],[Modified User Name],[Modified Date Time] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN (‘ + @FieldName + ‘,[Update Statement],[Modified User Name],[Modified Date Time])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])’
PRINT @sql
EXEC sp_executesql @sql
GO
EXEC dbo.Recover_Modified_Data_proc ‘oddball’, ‘rowoverflowtest’ ;
GO
This is the output that I see:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(139 row(s) affected)
(139 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(2 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 1793
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
[c1],[c2],[c3]
SELECT [c1],[c2],[c3],[Update Statement],[Modified User Name],[Modified Date Time] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([c1],[c2],[c3],[Update Statement],[Modified User Name],[Modified Date Time])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
I tried using the SQL debugger, but I did not see the error in the debugger and the line number given is larger than the size of the SQL script.
Do you know what is happening?
Hi Dann,
Can you please mention the SQL Server version and the collation details.
Will debug it and revert to you.
Thanks
Imran
The collation is SQL_Latin1_General_CP1_CI_AS.
The error that I got was on SQL Server 2014.
I get a similar error on SQL Server 2008 and 2012, but the line number is different:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
(137 row(s) affected)
(137 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(2 row(s) affected)
Msg 537, Level 16, State 2, Procedure Recover_Modified_Data_Proc, Line 650
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
(0 row(s) affected)
(0 row(s) affected)
[c1],[c2],[c3]
SELECT [c1],[c2],[c3],[Update Statement],[Modified User Name],[Modified Date Time] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([c1],[c2],[c3],[Update Statement],[Modified User Name],[Modified Date Time])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
Maybe the debugger will work with this instance, I have not tried it yet.
Thanks Dann, will test it in my lab and get back to you.
Imran
For the bit table, shouldn’t it start out with this value instead:
—-Create table to set the bit position of one byte.
INSERT INTO @bitTable
SELECT 0, 1
because 2^0 = 1
instead of :
—-Create table to set the bit position of one byte.
INSERT INTO @bitTable
SELECT 0, 2
It’s possible to make a program, when I just enter with the log record, and a procedure return the informations ? ( old inormations ? )
If yes, how ???
Hi
its not at all working for me.it’s showing empty record.haven’t got any error while executing.
Hi Sucheta,
Please check your SQL Server recovery model.
Imran
Hi Imran
I am getting the following error. please reply urgently..
and my table script as below:
CREATE TABLE [dbo].[ProfileIdentifiers](
[IdentifierID] [int] IDENTITY(1,1) NOT NULL,
[ProfileID] [int] NOT NULL,
[IdentifierType] [tinyint] NOT NULL,
[IdentifierKey] [varchar](30) NOT NULL,
[CreateDate] [smalldatetime] NOT NULL,
[FPCardBIN] [tinyint] NULL,
CONSTRAINT [PK__ProfileI__5EB5E79668487DD7] PRIMARY KEY CLUSTERED
(
[IdentifierID] 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 am using 2005 MS SQL and Error as below
Msg 229, Level 14, State 5, Procedure Recover_Modified_Data_Proc, Line 48
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 70
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 72
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 94
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 95
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 229, Level 14, State 5, Procedure Recover_Modified_Data_Proc, Line 111
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
(0 row(s) affected)
Msg 229, Level 14, State 5, Procedure Recover_Modified_Data_Proc, Line 152
The SELECT permission was denied on the object ‘fn_dblog’, database ‘mssqlsystemresource’, schema ‘sys’.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 173
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 175
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 284
A cursor with the name ‘Page_Data_Cursor’ does not exist.
Msg 16916, Level 16, State 1, Procedure Recover_Modified_Data_Proc, Line 285
A cursor with the name ‘Page_Data_Cursor’ does not exist.
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
SELECT [IdentifierID],[ProfileID],[IdentifierType],[IdentifierKey],[CreateDate],[FPCardBIN],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([IdentifierID],[ProfileID],[IdentifierType],[IdentifierKey],[CreateDate],[FPCardBIN],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
Hi,
Basically, you donot have the permission to execute fn_dblog in this database.
Please get the access and execute it again.
Imran
Hi Muhammad,I do this step by step, when I execute the PROCEDURE the sqlserver message console print it has 2 rows affected.
print this:
SELECT [Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([Sno],[Student ID],[Student name],[Date of Birth],[Weight],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(2 row(s) affected)
but select two null rows, can you help me? thanks
Hi Sam,
Can you please provide the sample data along with the table structure. I can test and update you.
Thanks,
Imran
DEAR IMRAN,
Server: Msg 8115, Level 16, State 6, Procedure Recover_Modified_Data_Proc, Line 438
Arithmetic overflow error converting varbinary to data type numeric.
The statement has been terminated.
It works fine in local DB..But not working in Remote Server DB..That is not giving results…Please give information how to run on remote server DB..
Hi,
Can you please give me the error message ?
Thanks,
Imran
Hi Imran,
Thanks a lot for the article. I would like to know weather the deleted rows can be recovered?
If yes please suggest me how it can be done…
Waiting for your response…
Warm Regards
Prithviraj
Thanks Prithviraj. Given below is the article that can help you to recover deleted data, if you do not have backup.
https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
Imran
This is a really great article. I have been able to recover rows for database trans log backups in the past but came across a situation where no data was returned. After some troubleshooting I realized it was because the table in question was compressed using PAGE data compression which I imagine would have changed the Row Contents structure. Do have you have any information about how the script could be altered to accommodate for this?
Hi Imran.
Impressive work. Thank you for making so much available.
I am trying to extend the procedure to see the history of changes (INSERTs, DELETEs, and UPDATEs) Adding the INSERTs and DELETEs was fairly easy. fn_dblog() records seem to contain all the information necessary to restore/reverse those actions.
However, modifications are a problem. You use the DBCC PAGE query to get that information. That query seems to only contain the info for the last transaction. Using your Student table example; if you delete one of the records after the update, the procedure is no longer able to recover the update for that record. It can for the other record. The issue seems to have been commented on back on May 28, 2012 at 10:05 am.
I can get the fn_dblog() record for the UPDATE that the DELETE touched. The record contains the previous data, but no longer contains the complete record data (which you use to create the restoring UPDATE command). I could not determine a reliable correlating method between entries provided by fn_dblog() to try chain backward
Do you have any suggestions on how to accomplish this?
If I can get it to work, I will gladly share the changes with you.
Thanks.
Kind Regards,
Gary
Hi Imran.
It appears that you do not comment on posts for this topic very often. We all have other work and priorities and this is an old topic.
Just want to post that I did get the procedure adjusted to recover INSERTs, DELETEs and UPDATEs, even when the DELETEs and/or UPDATEs modify the same record (stacked modifications). As long as the DELETE or most recent UPDATE is in the date range, the procedure will spin back through the multiple changes for the same record and show its complete history of changes over that period and how to restore the data to that point. I also removed most of the cursor operations that caused the procedure to run very slowly. The one that I didn’t remove was the LOP_MODIFY_COLUMNS process, which involves many intermediate steps during the recover process. It looks like it is now a very handy restoration process for a much more general case. Thanks for providing such a big head start to accomplish this.
Kind Regards,
Gary
Hi Gary,
Nice to hear that it helped you a lot. I am glad that you modified it and made it better. Please do share it with the community. In addition, please test it on multiple scenarios.
Let me know if you need any help.
Thank you
Imran
Hi Imran.
The code can be found at: https://github.com/wtw2/MS-SQL
in the “Recover data using log” folder.
It has not been rigorously tested yet. The testing table code is near the bottom of the file. The file is set up to create the stored procedure or to run the code for testing purposes with little modification (see near top). A bunch of debug code is in it also and its execution is controlled by the @Debug variable.
Kind Regards,
Gary
It awesome! Imran, you’re SQL-Monster! Thanks for script, I’ll apply this in my work
Hi Imran, Just a question
How can I modify this procedure. Perhaps include the Transaction ID as a parameter to improve the function.
I mean… I’ve got the modify records before (one by one ), and I just want to get the old values of one update as u do for all
Sorry about my English
Thanks anyway, Regards
Hi I am trying your code to recover accidentally updated table but neither i get any error nither the data is getting updated. Kindly help me on this.
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
check your table name…
the fellow guy using the table name dbo.student. is your table name is dbo.student.
Hi Imran ,
I am unable get recovery due to execution. Its taking a long time to execute but still not giving me back any result in my database. But when I using it in any small Database its giving result.
I am unable to understand why is it not working in my required database
If you need i can send backup of my database plz… help me its too important
Hi Chandan,
Please let me know your SQL Server environment & recovery model.
Thanks
Imran
Reblogged this on Keep Software Testing Weird and commented:
Recover Updated Data from SQL Server without BackUp
Msg 701, Level 17, State 123, Procedure Recover_Modified_Data_Proc, Line 111
There is insufficient system memory in resource pool ‘internal’ to run this query.
I got this message and I can’t Recover my data. Can help me plzzzz!!!
Hello Imran, first of all congratulations for the script you created.
I’m testing data recovery for command “UPDATE”.
When I update any column to the value ” error occurs. Any suggestions on how I can fix this?
Hi Imran, thanks for the script. I’m running 9.0.44035 but still getting “Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘(‘.”
Any thoughts? Thanks
hey bro u did great job thanx, but here is what i got:
why no result there is no way i didn’t make any update
Hi Imran,
Thanks for the script but i cannot create the procedure because I got following error while running the script. please i want help from you
Msg 137, Level 15, State 2, Procedure Recover_Modified_Data_Proc, Line 189
Must declare the scalar variable “@SlotID”.
Msg 137, Level 15, State 2, Procedure Recover_Modified_Data_Proc, Line 264
Must declare the scalar variable “@SlotID”.
Msg 137, Level 15, State 2, Procedure Recover_Modified_Data_Proc, Line 277
Must declare the scalar variable “@SlotID”.
Msg 137, Level 15, State 1, Procedure Recover_Modified_Data_Proc, Line 638
Must declare the scalar variable “@sql”.
Msg 137, Level 15, State 2, Procedure Recover_Modified_Data_Proc, Line 642
Must declare the scalar variable “@sql”.
Msg 137, Level 15, State 2, Procedure Recover_Modified_Data_Proc, Line 643
Must declare the scalar variable “@sql”.
It looks as if the script did not copy properly.
I did it again and when i run the procedure then i got following error please provide me some help.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
SELECT [CustTypeCode],[CustTypeGroup],[Description],[CBRCode],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([CustTypeCode],[CustTypeGroup],[Description],[CBRCode],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘(‘.
It is still not copied properly. Will send you the script.
Hi Imran,
I get the following message when I execute the stored procedure
UPDATE failed because the following SET options have incorrect settings: ‘NUMERIC_ROUNDABORT’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Hi bro, fantastic work! But I got an error below:
Msg 210, Level 16, State 1, Line 670
Conversion failed when converting datetime from binary/varbinary string.
The error should be in “WITH CTE”.
Could you please help fix that?
Many thanks in advance.
great article and very useful. Thanks for your community support.
Best
Sathiq
Hi bro, great work, but this stored procedure just show old data, how to show new data?
Thanks Muhammad Imran,
For this wonderful script. Can you please tell me. When i create a new database and update some value as per your given example i can see the change from your procedure but when i try to run this script on my table that i am using with my database it gives me error
Msg 8968, Level 16, State 1, Line 1
Table error: DBCC PAGE page (1:2439021) (object ID 0, index ID 0, partition ID 0, alloc unit ID 0 (type Unknown)) is out of the range of this database.
Hope you will reply me.
will u send the corrected procedure to me?
this my mailid:donguys.rahman@gmail.com
its so slow while processing large database
Hi!
Really impressive work!
But although I got Query executed successfully, rows affected are 0 while I would expect some thousand rows returned.
Perhaps reason is that I have backuped the transaction logs:
I have changed all
sys.fn_dblog(NULL,NULL)
into
sys.fn_dump_dblog(NULL, NULL,N’Disk’,1,@LogFile,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
and clearly added @LogFile as Input Parameter, but got this console log:
(0 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
SELECT [AboutMePage],[Active],[AspSessionID],[Authorized],[AuthStatus],[RegistrationDate],[RESTToken],[RevocationRequest],[RevocationTime],[SafePaymentExempt],[SellerGuaranteeLevel],[SessionIDA],[SessionIDAProcessed],[SessionIDV],[SessionIDVProcessed],[Site],[SiteCode],[SkypeID],[Status],[StoreUrl],[TopRatedProgram],[TransactionPercent],[TS],[UID],[UserID],[UserIDLastChanged],[WhatsAppNumber],[Update Statement] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN ([AboutMePage],[Active],[AspSessionID],[Authorized],[AuthStatus],[RegistrationDate],[RESTToken],[RevocationRequest],[RevocationTime],[SafePaymentExempt],[SellerGuaranteeLevel],[SessionIDA],[SessionIDAProcessed],[SessionIDV],[SessionIDVProcessed],[Site],[SiteCode],[SkypeID],[Status],[StoreUrl],[TopRatedProgram],[TransactionPercent],[TS],[UID],[UserID],[UserIDLastChanged],[WhatsAppNumber],[Update Statement])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])
(0 row(s) affected)
(1 row(s) affected)
do some parameters change when backup log, or can suggest what can be wrong?
Thanks anyway.
Hi Imran
It might be old post but it still helping even now.
I’m trying to recover updated XML data type column but the script is running past 3-4 minutes. It passed 30 minutes but there are only 306 records.
How can I recorver XML data type data.
Thanks
Hi Imran
I’m trying to recover xml data from update transaction but the script keep on running for almost an hour with no results.
I tried to add xml system id to the script but no luck.
May you please kindly assist.
Thanks