Click here to Skip to main content
15,892,927 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
i am working on live database server. and unfortunately i ran delete command in sql server which is wrong. so i want to recover my deleted records immediately.

any help for that....
backup and restore of database is not good idea for that because it is live server.


For Example : i have employee table and unfortunately i deleted all records then i need to recover it.

query :

Delete from employee....
Posted

Server keeps log for each deleted records.You can query these logs via 'fn_dblog' sql server function with the table name "Employee" and it will give you all the deleted records from the log.
SQL
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/
 
Share this answer
 
Lalit Kumar 5733 wrote:
i have employee table and unfortunately i deleted all records then i need to recover it.

:doh: Did a big mistake! oops...


Lalit Kumar 5733 wrote:
so i want to recover my deleted records immediately

Not possible... further with the restrictions you have... not at all!

Though if you have a DB backup, you are still saved and can restore it. Just need to put site under maintanence and do it.

Something similar was asked 2 days back.. see the responses:
http://www.codeproject.com/Answers/78808/Roll-back-a-delete-query.aspx[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900