[chandrikace]

how to get back deleted record in sql server 2005

[eclipsed4utoo]

you can't. once you have deleted it, it is gone forever.

[mikeblas]

If the DELETE was a part of a transaction, just roll back the transaction. If it wasn't, then the record really is gone. You'll have to restore it from a backup.

[mimran18]

chandrikace, on 30 December 2008 - 05:25 AM, said:

how to get back deleted record in sql server 2005

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.

 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/2...from-sql-sever/

[BobRodes]

A good way to avoid the problem is to create a "recordstatus" field, and set values to mean various things. "Deleted" could be the number 7, for example. That way, you can structure all of your select queries with a "where recordstatus <> 7" in them, and if you need to undelete a record just change its recordstatus to 1 or whatever. You can then periodically purge the database of deleted records when it gets too big. A good corollary idea is to datetimestamp the creation and last modification of the record; you can then purge any records that are older than a particular date as well.