CODEWITHSUNDEEP
sql-server
sumit
sumit

Reputation:

Need help in SQL Rollback transaction

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

Upvotes: 0

Views: 198

Answers (3)

John Sansom
John Sansom

Reputation: 41819

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.

Upvotes: 0

Eric
Eric

Reputation: 95133

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.

Upvotes: 4

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 4

Related Questions