How to undo a SQL Server UPDATE query?

In SQL Server Management Studio, I did the query below.
Unfortunately, I forgot to uncomment the WHERE clause.
1647 rows were updated instead of 4.

How can I undo the last statement?

  • ExecuteScalar always returns null when calling a scalar-valued function
  • Is it possible to use SqlGeography with Linq to Sql?
  • Filling gaps in pairs of start/end dates
  • Solution for previewing user changes and allowing rollback/commit over a period of time
  • How to improve performance on a clustered index seek
  • How to keep row order with SqlBulkCopy?
  • Unfortunately, I’ve only just finished translating those 1647 rows and was doing final corrections , and thus don’t have a backup.

    UPDATE [dbo].[T_Language]
           [LANG_DE] = 'Mietvertrag' --<LANG_DE, varchar(255),>
          ,[LANG_FR] = 'Contrat de bail' -- <LANG_FR, varchar(255),>
          ,[LANG_IT] = 'Contratto di locazione' -- <LANG_IT, varchar(255),>      
          ,[LANG_EN] = 'Tenancy agreement' -- <LANG_EN, varchar(255),>
           --WHERE [LANG_DE] like 'Mietvertrag'

    There is a transaction protocol, at least I hope so.

    4 Solutions collect form web for “How to undo a SQL Server UPDATE query?”

    A non-committed transaction can be reverted by issuing the command ROLLBACK

    But if you are running in auto-commit mode there is nothing you can do….

    Considering that you already have a full backup I’d just restore that backup into separate database and migrate the data from there.

    If your data has changed after the latest backup then what you recover all data that way but you can try to recover that by reading transaction log.

    If your database was in full recovery mode than transaction log has enough details to recover updates to your data after the latest backup.

    You might want to try with DBCC LOG, fn_log functions or with third party log reader such as ApexSQL Log

    Unfortunately there is no easy way to read transaction log because MS doesn’t provide documentation for this and stores the data in its proprietary format.

    If you already have a full backup from your database, fortunately, you have an option in SQL Management Studio. In this case, you can use the following steps:

    1. Right click on database -> Tasks -> Restore -> Database.

    2. In General tab, click on Timeline -> select Specific date and time option.

    3. Move the timeline slider to before update command time -> click OK.

    4. In the destination database name, type a new name.

    5. In the Files tab, check in Reallocate all files to folder and then select a new path to save your recovered database.

    6. In the options tab, check in Overwrite … and remove Take tail-log… check option.

    7. Finally, click on OK and wait until the recovery process is over.

    I have used this method myself in an operational database and it was very useful.

    Since you have a FULL backup, you can restore the backup to a different server as a database of the same name or to the same server with a different name.

    Then you can just review the contents pre-update and write a SQL script to do the update.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.