SQL Server Performance Forum – Threads Archive

emergency rollback

Hello all, i just got myself into a big problem. How do i rollback an update transaction that i just sent? Here was the code i used to update: update address set pcode = left(pcode,3) + ‘ ‘ + right(left(pcode,6),3) i stupidly left out the where clause. I was using microsoft sql server management studio that comes with .net 2005 to write the query. help is much appreciated

---

do you have any timestamp column in the table? or even a recent back up? or has the db been recently refreshed in another environment (like Qa/DEV) from where you can get the data for this table? ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

do u have Snapshot backup of the database. If yes it is easy? if not, if the database is Full Recovery model use backup restore using STOPAT method to create a new database
Madhu

---

luckily i averted a crisis. I luckily just made a backup just before the query. I restored that database and all is good. I am still wondering though is there tsql transaction code i can wrap an update statement that will give me the option to commit based on rows affected or other variables?

---

You can use explicit transaction… BEGIN TRAN
update…
IF @@ROWCOUNT > <Value>
ROLLBACK TRAN
ELSE COMMIT TRAN Note: You can create database snapshot before big update like this…
MohammedU.
Moderator
SQL-Server-Performance.com

---

thank you very much Mohammedu, i will use the transaction code you outlined. As for the database snapshot, i am unfamiliar with what that is. Ill have to look it up. Is that something you do from enterprise manager like a backup?

---

Database snapshot only available in SQL SERVER 2005 not in 2000. SQL Server 2005 Books Online
How to: Create a Database Snapshot (Transact-SQL)
http://msdn2.microsoft.com/en-US/library/ms175876.aspx MohammedU.
Moderator
SQL-Server-Performance.com

---

You would think that this code would be more commonly known. I too learned how to use this bit of code the hard way. There’s nothing worse than that feeling after you’ve just pressed F5 and realized you left off the where clause….

---

I do it in a similar way..Usually for Updates and Deletes I run the SELECT with the same WHERE condition first to get the number of rows that *should have been* affected. Then I do a:
BEGIN TRAIN UPDATE…
— COMMIT TRAN
— ROLLBACK TRAN I have the COMMIT and ROLLBACK commented. If the UPDATE affects the same rows as expected I execute the COMMIT else ROLLBACK appropriately. ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

so after you have begun the transaction which includes the update how exactly do you execute the commit/rollback? Do you comment out begin tran and the update and only execute "commit tran"? eg: –BEGIN TRAIN –UPDATE…
COMMIT TRAN
— ROLLBACK TRAN or do you run the entire transaction again this time with commit or rollback uncommented? BEGIN TRAIN UPDATE…
COMMIT TRAN
— ROLLBACK TRAN Thank you for all of the great feedback! PS. yes, i am working with sql server 2000 using the 2005 query analyzer.

---

I should have mentioned, when I have to do any update or delete manually through QA I use the approach I mentioned above. If its in a proc the only way is to check for @@ERROR. The only way to validate the UPDATE/DELETE us by doing a SELECT prior to the UPDATE/DELETE, get the @@Rowcount and compare. Then do a COMMIT or ROLLBACK. ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

When ever I do manual delete/update the data, I copy the data to different table…
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>