How to recover the old data from table.

Question

Hi,

I have made an update statement in a table in SQL 2008 which updated the table with some wrong data.

I didn't have a backup for the DB.

It's some important dates which got updated.

Is there anyway where i can recover the old data from the table.

Thanks

SNA

Answer 1

Basically no unless you want to use a commercial log reader and try go through it with a fine tooth comb. No backup of the database can be an 'update resume, leave town' scenario - harsh but it just should not happen.

Answer 2

Andrew basically has called it. I just want to add a few ideas you can consider if you are desperate:

1. Are there any reports or printouts lying around? Perhaps you can reconstruct the data from there.
2. Was this data entered via a web application? If so, there is a remote chance you can find the original data in the web server logs, depending upon how the app was constructed, etc.
3. Does this app interface (pass data to) any other applications? They may have a buffered copy of data...
4. Can the data be derived from any other existing data? Is there an audit log table, or another date in your schema based on this one, from which you can reconstruct the original date?

Edit:

Some commenters are mentioning that is is a good idea to test your update/delete statements before running them. For this to become habit, it helps if you have an easy method. I usually create my DELETE statements like this:

--delete --select * 
from [User]
where UserID=27

To run the select in order to test your query, highlight everything from select onwards. To then run the delete if you are satisfied with the filter criteria, highlight everything from delete onwards. The two dashes in front of delete are so that if the query accidentally gets run, it will just crash due to invalid syntax.

You can use a similar construct for UPDATE statements, although it is not quite as clean.

Answer 3

Always take a backup before major UPDATE statements, even if it's not used, there's the peace of mind

Especially with Red Gate's Object Level Restore, one can restore individual table/row now given a backup file

Good luck, I'd suggest finding an old copy elsewhere (DEV/QA) etc...

Answer 4

Isn't it possible to do a rollback on an UPDATE statement?

Answer 5

Thanks for all the responses.

The problem was actually accidentally ---i missed to select the where condition in the update statement.---Rest !.

It was a quick 5 minutes task --Like just changing the date to test for one customer data--so we didn't think of taking a backup.

Yes of course you are true ..This is a lesson.

Now onwards i will be careful to write "my update statements in a transaction." or "test my update statements"

Thanks once again--for spending your time to give some insight rather ignoring the question since the only answer is "NO".

Thanks

SNA