top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How do I get back some deleted records?

+6 votes
46 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
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...