Mistakenly ran an update statement on table

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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