Accidently update a table, How can I recover??

Hi, Guru,

I accidently update the wrong rows of one table in the MS SQL server through Query Analyzer. How can I recover the old result? Somebody said the "ROLLBACK". But it gave me the error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

I am new on the Database, how can I recover the old result? Thanks.

suwaneeAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You can't.  Do you have a backup, and can you modify the UPDATE query to effectively un-do the previous one?

The rollback you are referring to is when you use transactions in code, and surround your queries in them.  Using Access VBA it goes like this...
Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

cn.BeginTrans

cn.Execute "Query 1"
cn.Execute "Query 2"
cn.Execute "Query 3"

If Whatever then 'error trapping, records affected, whatever criteria you want...
   cn.CommitTrans
else
   cn.Rollback
End If

Hope this helps.
-Jim
0
 
suwaneeAuthor Commented:
No, I don't have backup, and I don't know the old results. It got to be a problem.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You may be hosed here.  Can you re-import the table from whatever the source of its data was?

Sorry.
-Jim
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
jrb1Commented:
There are a couple of products that might help you (free trials).  They both read the transaction logs and allow for changes to be undone.

Go here:

http://www.apexsql.com/frame_downloads.htm

Look for ApexSQL Log.  It is supposed to generate Undo scripts for you.

Or try Lumigent's Log Explorer:

http://www.lumigent.com/products/le_sql.html

You should be able to "Undo a single operation or multiple transactions" with this product.
0
 
infolurkCommented:
On the bright side, you may have your first really good example of "Why we must do backups". If you cant recover the data you may want to keep a record of this incident to refer to whenever you are getting a bit lazy on the backup front, or when someone tells you that you dont really need backups.

Cheers
SFH
0
 
Julian HansenCommented:
Here is a tip for the future.

I had a similar problem a while back and it cost me dearly. Since then I do the following when making any data changes to my database.

Assume QA

Before making changes to the database run the following command

BEGIN TRANSACTION

Now do all the updates you want - make sure you don't close your session or you will kill the transaction frame

After updates check that all is well - if you are happy run the following

COMMITT TRANSACTION

if you are not run

ROLLBACK TRANSACTION

This is not a substitute for backups but is a useful technique to avoid having to use the backups.

Julian
0
 
infolurkCommented:
I think Jim and Jrb both supplied pertinent answers given the information they had at the time. I feel my advice is just as applicable as julians, given that by the time julian answered we knew that the transaction had not been declared. For future use, my advice on backups is just as if not more useful than transaction rollback syntax.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.