How do I get back some deleted records?

Question

If I by mistake delete a large amount of rows from the remote server, Is there any way to get back these records?

Answer 1

SQL Server keeps logs for each deleted record. You can query these logs via the fn_dblog SQL Server function.

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.

The article below will help you recover the deleted records in the way defined above:

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

Credit: http://dba.stackexchange.com/questions/995/how-do-i-get-back-some-deleted-records