Rollback or Undo - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Rollback or Undo

Author

Topic

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2011-03-01 : 15:40:06

I've searched and searched, but couldnt find anything so I'm here to ask. When you make a change in the DB by an update statement and the realize you made a mistake, is there a way to rollback/undo what you did?

I only ask because a person on our team updated a field, but to all the records in the database. Person forgot to add the WHERE clause. We're all good because we have backups, but wanted to know if we could roll back that change with some kind of ROLLBACK code.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-01 : 15:43:59

BEGIN TRAN

UPDATE myTable99 SET Col1 = 'xxx'
-- WHERE Col2 = 'yyy'

ROLLBACK TRAN

Unless a transaction is coded, the transaction is implicitly committed

(Which is the opposite of Oracle)

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/

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2011-03-01 : 15:51:37

It was just a plain update statement like below and nothing else. Will what you provided above work? Thanks.

Update Table_name
Set col = 'xx'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-01 : 15:58:50

What Brett is saying is that in order to undo the update, you would have had to specify a transaction. The user didn't do this, so you can't undo it. Restoring from backups is your only option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2011-03-01 : 16:14:41

Got it thanks.

yukiever
Starting Member

13 Posts

Posted - 2011-03-01 : 16:32:01

quote:
Originally posted by ggarza75

It was just a plain update statement like below and nothing else. Will what you provided above work? Thanks.

Update Table_name
Set col = 'xx'

You cannot undo this..? T.T
I did the same mistake.........1 minute ago.
I also forgot the WHERE statement and I am in trouble...

Heres my code:
UPDATE test_table31 SET weather='Sunny'
Should be added (WHERE forecast='Sunny')

Plz help
this is SQL Server 2008 (I am using MS Server Management Studio)

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2011-03-01 : 16:40:50

Since you did exactly what my team member did, you'll need to get your old values from the most recent backup. Thats what we had to do.

yukiever
Starting Member

13 Posts

Posted - 2011-03-01 : 17:31:05

quote:
Originally posted by ggarza75

Since you did exactly what my team member did, you'll need to get your old values from the most recent backup. Thats what we had to do.

Thanks. I didn't have backup so I created the table again...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-01 : 17:33:25

Hopefully you've scheduled a recurring backup now to avoid recreating data in the future.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-01 : 17:52:53

quote:
Originally posted by yukiever

quote:
Originally posted by ggarza75

Since you did exactly what my team member did, you'll need to get your old values from the most recent backup. Thats what we had to do.

Thanks. I didn't have backup so I created the table again...

You're lucky can do that...how is that possible?

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources