top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How do I get back some deleted records?

+6 votes
328 views

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

posted Feb 19, 2014 by Asmita Agrawal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+2 votes
 
Best answer

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

answer Feb 19, 2014 by Salil Agrawal
...