6

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?

Improve this question
2
  • 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, 2015 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, 2015 at 16:27

2 Answers 2

Reset to default
4

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).

Improve this answer
2

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

Here are nice articles how to achieve it:

Improve this answer
3
  • 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, 2015 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, 2015 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, 2015 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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