How can I rollback an UPDATE query in SQL server 2005?

Question

How can I rollback an UPDATE query in SQL server 2005?

I need to do this in SQL, not through code.

Answer 1

You can rollback the statements you've executed within a transaction. Instead of commiting the transaction, rollback the transaction.

If you have updated something and want to rollback those updates, and you haven't done this inside a (not-yet-commited) transaction, then I think it's though luck ...

(Manually repair, or, restore backups)

Answer 2

begin transaction

// execute SQL code here

rollback transaction

If you've already executed the query and want to roll it back, unfortunately your only real option is to restore a database backup. If you're using Full backups, then you should be able to restore the database to a specific point in time.

Answer 3

Once an update is committed you can't rollback just the single update. Your best bet is to roll back to a previous backup of the database.

Answer 4

From the information you have specified, your best chance of recovery is through a database backup. I don't think you're going to be able to rollback any of those changes you pushed through since you were apparently not using transactions at the time.

Answer 5

You need this tool and you can find the transaction and reverse it.

ApexSQL Log

Answer 6

As already stated there is nothing you can do except restore from a backup. At least now you will have learned to always wrap statements in a transaction to see what happens before you decide to commit. Also, if you don't have a backup of your database this will also teach you to make regular backups of your database.

While we haven't been much help for your imediate problem...hopefully these answers will ensure you don't run into this problem again in the future.

Answer 7

If the update has already occured then the best solution is probably to restore the database from a recent backup. If you only have full database backups, then you can only restore to the time of the backup as mentioned by many.

If you also have transaction log backups, you might be able to restore to the point in time just before to update occured.

Also for the future, it might be worth placing your update SQL within a transaction, as well as making your database back up the transaction log to help with rollbacks. (if not already)