How to undo a SQL Server UPDATE query?

Question

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?

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]    SET         [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.

Answer 1

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....

Answer 2

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.