3

Is it possible to rollback a committed transaction?

I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit transaction command. Now is it possible to rollback and get the data back?

  • I know v. little about SQL Server - but is there any sort of PITR (Point In Time Recovery) setup in your shop? – Vérace Jul 1 '15 at 13:27
  • Plain answer you cannot rollback if it has committed or auto committed. yes you can use various methods of restore to get back your data – Shanky Jul 1 '15 at 16:27
3

No. Committed transactions modify the contents of the database tables first in the transaction log, then in the data files. Unless triggers or other techniques are explicitly set up upfront, there is no way to recover the values stored in the database before the transaction was committed.

In order to do that, you have to restore a copy of the database before the transaction ended.

Other techniques rely on reconstructing the data reading the transaction log, but I don't recommend them: restoring a copy of the database is much easier (and supported).

1

If you haven't trimmed the transaction log, you can do it.

Here are nice articles how to achieve it:

  • Yes this is the correcter answer. It's true, that you can restore a committed transaction using the transactional restore. By the way, please include the solution to your answer as the link may change over time or disappear. :-) – Ionic Jul 1 '15 at 13:36
  • 1
    I advise against this solution as the primary way to solve the issue. It's unsupported and won't work if the log has been truncated. Restoring the database up to the point of the unwanted transaction is supported and guaranteed to work, so why bother messing with undocumented stuff? – spaghettidba Jul 1 '15 at 14:43
  • The answer would still be NO you are not rolling it back you are doing recovery you found out what was changes and you used Trn log backup to do a PIT. Plus what was demonstrated was for very small data but you will find it very difficult if data is big. – Shanky Jul 1 '15 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.