Reading Transaction Information from SQL Server LOG

Posted by Sarvesh under Sql Server on 9/13/2009 | Views : 2563 | Status : [Member] | Replies : 2
Hi sql DBAs,

Is there any way to findout the original value of a column after modified it ?

ie:
I have one table : tbl_transaction_history(tranid, tranhistory,.........)
Original Value is :
tranid = 100
tranhistory = 'This is my test information'

I am just modifying the data from 'This is my test information' to 'This is modified'

update tbl_transaction_history set tranhistory ='This is modified' where tranid = 100

the transaction also successfully committed.

now the modified Value is :
tranid = 100
tranhistory = 'This is modified'

But, we don't used any audit maintenance.

My question is : I just want to know the ORIGINAL value.

thanks in advance
sarvesh s




Responses

Posted by: Radhadeep on: 5/29/2013 [Member] Starter | Points: 25

Up
0
Down
You can use OUTPUT clause to get the old value

CREATE TABLE Ids (Id INT PRIMARY KEY)

INSERT Ids VALUES (1)
INSERT Ids VALUES (2)
INSERT Ids VALUES (3)

SELECT * FROM Ids
GO

DECLARE @Tab TABLE(Id INT)
UPDATE Ids SET Id = 4
OUTPUT deleted.Id INTO @Tab
WHERE Id = 3
SELECT * FROM @Tab


Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 9/13/2009 [Member] [MVP] Silver

Up
0
Down
Hi Sarvesh

My understanding is , You want to know the previous value of a Data after modified it. If so..

YES. we can read the transaction from LOG by..

1.Third party tool.
2.Fn_DBLog function( The transaction history will be in Hex format, We have to convert into Text form that is it )

You have the following columns like...
RowLog Contents 0
RowLog Contents 1
RowLog Contents 2
RowLog Contents 3
RowLog Contents 4
Log Record

The columns of Fn_DBLog function will have the actual / modified track history. From these columns' Hex data we can convert into Text format.

Pl try and get back for furthor clarifications.

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response