Sometimes, database developers need to work with a database in which they don't have any permission to add any procedure or trigger, because that database is for someone else but they need change tracking. In this tip, I will show a solution for this problem.
Background
First of all, I want to appreciate Muhammad Imran for the great information on his site:
http://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1.
Then, as a brief history and description: In SQLServer, the log file (LDF) is a Microsoft solution for implementing transactions, and rollback action uses the log file for rolling back, so we can use the LDF file to track data.
Using the Code
SQL Server has undocumented T-function
sys.fn_dblog that is used for reading log file and procedure DBCC PAGE that is used for details of data page like
allocation_UnitId,
Fields,
Values,
SlotId.
Collapse | Copy Code
Create PROCEDURE Find_UpdatedColumn_In_Table
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(MAX),
@Date_From datetime='1900/01/01',
@Date_To datetime ='9999/12/31',
@FeildName nvarchar(max)
AS
begin
DECLARE @parms nvarchar(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @ConsolidatedPageID VARCHAR(MAX)
Declare @AllocUnitID as bigint
Declare @TransactionID as VARCHAR(MAX)
Declare @Operation as VARCHAR(MAX)
Declare @DatabaseCollation VARCHAR(MAX)
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
SELECT [PAGE ID],[Slot ID],[AllocUnitId]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') _
AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
AND [TRANSACTION ID] IN (SELECT DISTINCT _
[TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
AND 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)
SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX_
(':',@ConsolidatedPageID)) SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX_
(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))
SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary_
(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', _
'varbinary(max)')) FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
DELETE @temppagedata
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + _
@fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;');
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,_
[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID
END
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)
)
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')
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]ORDER BY [Slot ID]
UPDATE @ModifiedRawData SET [RowLog Contents 0] = cast('' AS XML).value_
('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
FROM @ModifiedRawData
select Field, Value from @temppagedata t
inner join @ModifiedRawData m on LTRIM(RTRIM(SUBSTRING_
(t.[ParentObject],5,3))) = cast(m.[Slot ID] as nvarchar(1000))
where t.Field = @FeildName
end
EXEC Find_UpdatedColumn_In_Table 'Test','dbo.Test','2000/01/01','9999/12/31',N'id'
Note
Something to note is that this proc just works with the current log, and if we need to work with log back up, we need to restore.
By
Mehdy Moini,
13 Sep 2013
