CODEWITHSUNDEEP
sql-server
user3322057
user3322057

Reputation: 1

Undo update query in sql server 2012

I am new to SQL, so as a newbie I have done a mistake. I executed update query without where clause and now instead of upating just one row it has updated all rows. Now I want to undo it. I googled it and there was options to run ROLLBACK. But my query was

update tblname set mode=''

So I think rollback will not work. Also I have no backup configured so I cannot restore it as well. I tried using SQLApexlog. But was not successful.

So is there any way I can get all records back.

Upvotes: 0

Views: 4980

Answers (3)

Milena Petrovic
Milena Petrovic

Reputation: 2771

ApexSQL Log can help with accidental updates. See more info here: How to recover SQL Server data from accidental updates without backups

However, keep in mind that it gives the best results when the database is in the full recovery model and when you have a full database backup.

But there were 5000+ records were updated so I have do select all those 5000+ records to UNDO

Yes, but that's easy to select. To recover all updated records, first select only the table where the data was accidentally updated (or Server process ID, if you can nail it down). Make sure all other tables are not selected.

enter image description here

In the Operations tab select only Update row

enter image description here

And finally, in the main grid select all rows using the context menu (right-click anywhere in the grid). Actually, you don't even have to select the rows in the grid to create the undo script, just click Create undo script in the menu.

And there is even a quicker way to create the undo script for all records. Instead of opening the results in the grid, use the Export results to file option

enter image description here

Disclaimer: I work for ApexSQL as a Support Engineer

Upvotes: 4

Raging Bull
Raging Bull

Reputation: 18737

As a newbie, it could happen to anyone. The most important thing is, you should never let it happen again. There is a tool called ApexSQL to rollback the transactions. Download it here.

Upvotes: 0

TomTom
TomTom

Reputation: 62093

No. Done. Take it as a lesson. Mistakes:

  • Execute manual queries agaisnt production database. Always do that against a copy, script them out.
  • Not taking a backup.

Upvotes: 2

Related Questions