Siusic Dot Com

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

t doesn’t mean that the changes you made in a transaction, for example a delete operation, can not be undone.

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:

Delete Can Not be Rolled Back

MySQL vs SQL Server in Data File Management

How to Separate Data and Indexes of a Table into Different Files

How to Solve Problem That Excel Data Imported Into SQL Server Via DTS Became Null

Batch Script on Windows: Using For Loop to Rename All Files in a Folder