This question already has an answer here:

select * from aa

update aa set City='chennai',LastName='vinoth';

ID  FirstName  LastName  City
29  Abcrdrr    vinoth    chennai
1   John       vinoth    chennai
2   Joe        vinoth    chennai
35  raja       vinoth    chennai
38  Johsdfgn   vinoth    chennai

I have wrongly updated Lastname, City columns in all the rows. Now I want to rollback to the old table rows.

Using SQL-Server 2008R2.

marked as duplicate by Aaron Bertrand Jun 5 '14 at 12:42

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • To clarify: you want to get back the data as it was before the update? – Blaž Dakskobler Jun 5 '14 at 6:46
  • yes I want to get back the data as it was before the update – Vinoth _S Jun 5 '14 at 6:57
up vote 16 down vote accepted

Unless you have some kind of history table & trigger in place, to retain old values at every change, or you made a copy of the table before you ran the update, you will need to use the last backup that was taken before that update. Restore it (as some temp database) and extract the data.
You do have a backup available, right?

BTW, next time you're doing an update, I suggest you set it up like this:

SELECT *
-- UPDATE t SET Column1 = x, Column2 = y
FROM MyTable AS t
WHERE ...

Run the SELECT first to see exactly which rows will be updated. If needed, adjust the WHERE clause to get the rows you want to target. Only then mark the sentence from the UPDATE till the end and execute it. Much safer then running a "blind" update, as you did, forgetting the WHERE clause (I assume that was the problem).
Even safer - do it on some test database first :).

  • No i Don't have Backup? – Vinoth _S Jun 5 '14 at 6:59
  • Well if you don't have some copy of the data as it was before the UPDATE, I can't think of a way you can get it back. You should set up a backup & restore strategy as soon as possible. – Blaž Dakskobler Jun 5 '14 at 7:24

Another approach would be to use fn_dblog to check within Transaction logs. That is quiet advance topic, so would refer to an excellent article How to read and interpret the SQL Server log - by Remus Rusanu

To avoid in future, you can always use Transactions

BEGIN TRAN
BEGIN TRY
  update tbl set City='chennai',LastName='vinoth' from aa AS tbl;

  -- if update is what you want then
  COMMIT TRAN
END TRY
BEGIN CATCH
  -- if NOT then
  IF @@TRANCOUNT > 0
      ROLLBACK
  THROW
END CATCH

Edit: When writing complex T-SQL, you should use a TRY-CATCH block with TRANSACTION so you don't lock resources if an exception happens or the query gets cancelled. Otherwise, if the query is not complex, it is best to not use transactions.

Also, as Blaz mentioned, its always good to take backup of the database or just the table before doing any changes to the database.

  • 1
    Never ever use Transactions without try/catch. Or you risk preventing read/write so long as your connection string remains open if it has a failure or you kill it mid run. You can put throw in the catch statement to allow failures. – TamusJRoyce Dec 19 '17 at 20:34
  • @TamusJRoyce I was just running some Transaction updates (via SSMS) on a dev DB without commiting and then attempting to connect to the same DB with another client. The client failed to connect until I either a) commited the transaction or b) rolled the transaction back, so it is very good you mention this. – raddevus Jan 17 at 20:08
  • 1
    The answer does not emphasis on how to use transaction with try/catch. The answer states that you start an explicit transaction and do your work and then commit or rollback. Its always a best practice to do error handling, but thats a different point of discussion. – Kin Jan 17 at 20:50
  • Another note is that a transaction itself does not help prevent data from being lost. Even if an explicit transaction is not specified (above is an explicit transaction), a transaction still occurs. But an explicit transaction is useful if you also backed up the table in the transaction. Either both the backup and update would occur or neither would occur. – TamusJRoyce Jan 18 at 17:06

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