patternsqlMinor
Rollback Committed transaction in SQL Server
Viewed 0 times
rollbackcommittedsqltransactionserver
Problem
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 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?
Solution
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).
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).
Context
StackExchange Database Administrators Q#105660, answer score: 4
Revisions (0)
No revisions yet.