Recover Data Using Transaction Log
January 5th, 2008 by Andrew Chen
In my previous post, “Delete Can Not be Rolled Back”, I pointed out that a committed delete statement can not be rolled back. It is correct in the context of running transaction. Once a transaction is committed it can not be rolled back. However i
There are a few ways to undo a committed statement. One way is to use point in time restore to restore the database. But that will also undo changes that were made after the point in time. Another way is to find the data before the change and apply it back to the database and that has to reply on the transaction log. The transaction log if not truncated records every changes you made so if you can read the transaction log and find the specific changes you made you can undo it. However SQL server doesn’t provide you an out of the box tool to read transaction log. You will have to reply on third party tools like ApexSQL Log or Log Explorer. These tools can read SQL server transaction log and allows you to undo the statements already executed. In that sense, Pinal Dave’s post, “SQL SERVER - TRUNCATE can’t be Rolled Back Using Log Files after Transaction Session Is Closed” was right. If a database is in full recovery mode, a delete statement can always be undone using those transaction log reading tools but Truncate statement may or may not be undone.
I would hope Microsoft can provide such log reading tools
Related Posts:
(1 votes, average: 5 out of 5)
Loading ...
Great!. Theory is applied well in practice.
This post gives the most correct explanation of the delete and truncate operations. There is no free lunch either way!!. The concept of delete and truncate are very different. However in data protection stand point both work similer. Only difference is in the operational stand point where truncate frees lots of resources during its course. The comitted transactions can never be rolledback howver can be recovered to prior point in time using properly done full/diff/t-log backups. Even the log reader is not useful if the truncated/deleted data is changed by ‘following transactions’. So there must be some amount of redoing the work from users stand point. This can not be avoided and there is no possible logic which can avoid this situation. Hence this is the known limitation for ever.
Surprised not to see a single comment on this post.
- Durairaj Padhmanaban.