Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have mistakenly ran an update statement on one of our Columns in a SQL table. We don't have a backup of the table nor the database. Can any one help us to retrieve the values of the Column before the update.

share|improve this question

migrated from stackoverflow.com Jun 21 '10 at 15:14

This question came from our site for professional and enthusiast programmers.

add comment

4 Answers 4

I'm sorry to say that you've just taught yourself a lesson.

Is there any way to re-derive the data from other sources, maybe historical log files? Frequently we keep logs that record when things were changed and by whom; such a thing would help you find the last value of something.

For future reference, when I write an UPDATE statement, I always first type

UPDATE mytable SET qq=ww WHERE {where clause}

That is, I literally type "qq=ww", which is bound to be illegal, and type the actual WHERE clause before I type anything that could be actually executed. I think the backward order of the clauses is a design error in the SQL language.

share|improve this answer
1  
+1 the worse lessons to learn are the ones that are the biggest PITA in the process :-( –  Bart Silverstrim Jun 21 '10 at 15:26
add comment

If you still have the current log file it may be possible to recovery the data using a 3rd party tool. UPDATE statements are fully logged in most cases. Here are a couple of tools to look for:

Apex SQL Log by ApexSQL

SQL Log Rescue by RedGate

If you were running in simple recovery model with no backups, then ran your UPDATE statement, and then took a backup you've lost the ability to recover from your current log. In simple recovery model the log file gets truncated when you take a backup.

share|improve this answer
add comment

Before you run a bulk update, you should always do a backup of every table that's going to be affected.

It makes you stop and think, if nothing else. I've seen people do this in a transaction, and then immediately commit it without checking to see if it's right and not just successful.

share|improve this answer
add comment

Also, another reason to do things like this in a transaction.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.