48

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
  • 2
    Have you ever taken a backup? What is your recovery model set to? – Martin Smith Jan 14 '11 at 13:37
  • 6
    Oh thank god, I have an automatic backup from today afternoon. – Stefan Steiger Jan 14 '11 at 13:39
  • 5
    `SELECT name,recovery_model_desc FROM sys.databases` – Martin Smith Jan 14 '11 at 13:42
  • 1
    name -> CENSORED, recovery_model_desk -> FULL – Stefan Steiger Jan 14 '11 at 13:50
  • 3
    You also need to check that the database isn't in `auto_truncate` mode (it doesn't sound like it will be which is good!) `SELECT name,recovery_model_desc, CASE WHEN last_log_backup_lsn IS NULL THEN 'On' ELSE 'Off' END as AutoTruncate FROM sys.databases d join sys.database_recovery_status r ON r.database_id=d.database_id` Assuming that this is the case then you should be good to use your backup and do a point in time restore (do this as a new database to be safe). More details http://msdn.microsoft.com/en-us/library/ms190982.aspx – Martin Smith Jan 14 '11 at 13:51
  • Thanks, no it isn't in AutoTruncate -> OFF – Stefan Steiger Jan 14 '11 at 13:55
  • Interesting defaults, looks like somebody already made the experience. – Stefan Steiger Jan 14 '11 at 13:56
  • [Good answer here: If you're in FULL recovery then you can use SSMS to restore to point-in-time](http://stackoverflow.com/q/16945254/2662901). Now, why you can't undo a specific transaction, especially in an SSMS session, I would like to know.... – feetwet Dec 24 '15 at 19:53

6 Answers6

22

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

18

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
MojtabaRezaeimehr
  • 359
  • 5
  • 17
5

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.

Frank Baker
  • 146
  • 1
  • 4
0

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.

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • It won't be necessary, I realized, I made an update-script about 2 hours ago (hadn't saved it however, and wanted to discard it - luckily hadn't yet done so.). I updated all values, and have only made 4 changes since (which i remember), so I have not lost more than 5 minutes work. Wow, I'm one hell of a lucky guy... – Stefan Steiger Jan 14 '11 at 14:06
0

If you can catch this in time and you don't have the ability to ROLLBACK or use the transaction log, you can take a backup immediately and use a tool like Redgate's SQL Data Compare to generate a script to "restore" the affected data. This worked like a charm for me. :)

Will Strohl
  • 1,646
  • 2
  • 15
  • 32
0

I have a good way to undo or recovery databases using SQL Server Manager. by following the below points :

1 - Go to Database from left side -> Right-click -> Task-> Restore -> Database. enter image description here

2- Go to Timeline then select your time.

enter image description here

Abd Abughazaleh
  • 4,615
  • 3
  • 44
  • 53