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
Select [RowLog Contents 0]
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".
for this I am referring a beautiful blog written by "Mr. Mahammad Imran".
Article named "How to recover deleted data from SQL Server"
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.
reading transaction log data and recovering lost database data by rolling back
transactions to their original state.
The Article references is
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.
Hope you like it.
Posted by: MR. JOYDEEP DAS
There is good news that I want to share with all of my readers that ApexSQL provide me "ApexSQL Universal Studio bundle with 1 year subscription" free.
ReplyDeleteThanks to ApexSQL.
This is really fantastic article
ReplyDeleteThanks to Irfan also for original post.
This comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete