Need help in SQL Rollback transaction

Question

Hi all,

I have updated my database with this query:

 UPDATE suppliers SET contactname = 'Dirk Lucky'

So all the rows in the suppliers table have the contact name: "Dirk Lucky."

How can I rollback this transaction? I want to restore my the contactname column in the suppliers table.

Thanks, Programmer

Answer 1

Sounds like your transaction has already been committed. You can't roll it back anymore.

Your only option is restoring a backup. You might be able to restore a backup as a new database, so you can copy only the contactnames and not lose any other changes.

Answer 2

If you have the full log in FULL mode, you can do a restore from the log. There's an excellent blog post about it here.

If you don't, I seriously hope that you have a backup.

For future reference: When I do updates, I use the following syntax:

SELECT *
--UPDATE a SET col = 'val'
FROM myTable a
WHERE id = 1234

That way, you can see what you're selecting to update first. Then, when you're finally ready to update, you just select from UPDATE down and run the query. I've caught myself many times with this trick. It also works with deletes, so that's a bonus.

Answer 3

Hopefully your database is using the Full Recovery Model. If so:

First you need to take a transaction log backup now.

You can then look to perform a database restore from your FULL backup + Differntials.

Once done, you can then restore the transaction log to a specific point in time prior to your update statement.

See "How to Restore to a point in time"

As other posters have suggested, you could restore to another database, and apply an update back to the live database should you wish to minimise downtime.

Hope this makes sense but let me know if you need assistance.