Accidental DELETE and UPDATE

Accidental DELETE and UPDATE

Introduction

Accidental DELETE and UPDATE?  Data loss…

This article gives us a clear idea about it and what situation we can recover it.

When we can recover

1.    If we perform DELETE and UPDATION within 

     TRANSACTION a simple ROLLBACK  

     TRANSACTION can solve this problem.

2.    If you using Logged or Audit data by using Trigger or similar.

3.    If you take regular backup of our database.

If this scenario not supports, it is not possible to retrieve deleted or updated records, easily from MS SQL Server.

So What the Alternate

There are some alternate solutions that we can use in case of Accidental DELETE and UPDATE.

1.    SQL Server keeps log for each deleted records. 
     We can query these logs via fn_dblog MS 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' )

      for this I am referring a beautiful blog written by "Mr. Mahammad Imran".

     Article named "How to recover deleted data from SQL Server"

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

2.    ApexSQL Log by ApexSQL data driven is a SQL Server recovery tool capable of 
    reading transaction log data and recovering lost database data by rolling back     
    transactions to their original state.

    The Article references is

    http://solutioncenter.apexsql.com/2012/11/how-to-recover-sql-server-data-from.html

References

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/http://solutioncenter.apexsql.com/2012/11/how-to-recover-sql-server-data-from.html

Hope by this we can solve our problem. But I personally preferred ApexSQL Log. If have you any suggestion please make comments.