Thursday, 4 July 2013

View the deleted records from a sql server table?

DBCC LOG(MyDatabase,-1)
SQL Server keeps log for each deleted records. you can query these logs via '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.Given below is the article that can 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/

No comments:

Post a Comment