How to execute roll back?

Question

Post reply

How to execute roll back?

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
April 6, 2010 at 5:05 am

#140147

I executed one update query. It successful. But instead of one row it updates two rows value. I dont know that second row values before updating. Now I want to rollback this action want can I do.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Is this within a transaction ?

Im guessing not. If it is , you simply have to "ROLLBACK".

If not , then there is nothing the system can do for you.

You will have to find a backup , you do do backups right ?, and find the old value.

Clear Sky SQL
My Blog[/url]

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 2

chandrasekaran.ganapathy

SSCommitted

Points: 1738

April 6, 2010 at 5:19 am

#1146390

What is the command to get the back up value?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

There is no backup 'value' , there are only database backups.

Clear Sky SQL
My Blog[/url]

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 4

chandrasekaran.ganapathy

SSCommitted

Points: 1738

April 6, 2010 at 5:30 am

#1146396

How to do database back up?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

See here on howto backup a database http://msdn.microsoft.com/en-us/library/ms187510.aspx

However, if you have not backed up this database , you have lost the value you have overwritten.

Clear Sky SQL
My Blog[/url]

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

It sounds like you're just getting started working with databases. I'd suggest a few things. First, be sure that you're not doing these experiments and learning tasks with your companies production data. If you destroy data of value to the company, your position there could be in jeopardy. Second, when experimenting with any statement that modifies data, unless you know, absolutely, 100%, that the statement will work without flaw, it's always best to perform the query like this initially:

BEGIN TRANS

UPDATE dbo.MyTable

SET Col1 = 'A'

WHERE Col1 = 'B'

ROLLBACK TRANS

With this, you don't actually modify the data, but you can look at the row count to ensure you're only affecting the number of rows you expected to affect. Another approach, even safer, is to do this:

SELECT COUNT(*)

FROM dbo.MyTable

WHERE Col1 = 'B'

This allows you to verify that the WHERE clause you're going to be using in the UPDATE statement works to bring back only the data you want it to.

This type of defensive programming will help protect your data.

Then you need to go and read up on what a database backup is, how and when to implement them, and the repercussions of running a restore.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

I would also add a recommendation in addition to what Grant Recommended that you backup the table prior to changing data.

This is done in the following fashion:

Select *

Into MyTableBak20100406

From MyTable

I like the extra precautionary step. Should something go wrong, then you at least have the data as it was represented prior to the change.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events