Undo the last run query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Undo the last run query

Author

Topic

akki1563
Starting Member

10 Posts

Posted - 2012-04-02 : 07:38:22

Hi,
I ran one update query without specifying where clause..The update query is not inside Transaction. Is there any way to roll back this update statement ?

Please let me know.

Regards,
Akki

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-02 : 08:52:18

Ouch! None that I know of, Akki.

The only option that I know of is to restore your latest backup (to another location), get the data as it existed before the update, and then carefully recover the data you need.

Often times, when I have to make critical changes on a production server, I do the following:


-- 1
BEGIN TRAN
UPDATE SEcurities SET MarketPrice = 598.55 WHERE TICKER = 'AAPL';

-- 2
ROLLBACK

-- 3
COMMIT

I would run step 1, take a quick look to make sure it updated only one row, and then run step 3 if all seems well. Even this is dangerous on a production server, because you may be locking up a page, or more or locking up some hot rows etc.

akki1563
Starting Member

10 Posts

Posted - 2012-04-02 : 09:59:40

Thanks sunitha.

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-02 : 10:17:43

Do you have a backup before the UPDATE?

If yes, you can restore the backup to a different database name, the copy the data over

Or

Maybe you can use lumigent log explorer

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-02 : 12:35:21

There are some log reader tools available, with which you may be able to "undo" the update.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources