How to retriew orignal record in table

Posted by Shoyeb under Sql Server on 5/7/2013 | Points: 10 | Views : 1139 | Status : [Member] | Replies : 2
Hi All,

i have deleted record from table in sql servere accidently .. now how can i retriew whole record .. plz reply me .. i am in trouble




Responses

Posted by: Rickeybglr on: 5/7/2013 [Member] Starter | Points: 25

Up
0
Down
hi,
yes u can via below steps:

Server keeps log for each deleted records.You can query these logs via 'fn_dblog' sql server function with the table name and it will give you all the deleted records from the log.
Collapse | Copy Code
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' )

But this log is in Hex format. and you need to convert this Hex format to your actual data.
Given below is the article will help you to recover the deleted records in the same way defined above.

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/



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

Posted by: Sriramnandha on: 5/12/2013 [Member] Starter | Points: 25

Up
0
Down
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' )

sriram

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

Login to post response