Reading Transaction Information from SQL Server LOG

Posted by Sarvesh under Sql Server on 9/13/2009 | Views : 3951 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts