Recover data for a table

Question

Recover data for a table

kannan_188

Grasshopper

Points: 13
More actions
August 14, 2009 at 1:39 am

#213405

I have run an update query to update field of a table but forgot to write the where clause.I have not used begin tran. Neither I have the backup of the DB. How to restore the old values of the table???

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you have absolutely no backup of the database at all, you don't get the data back. That's one of the things that backups are for.

You could try a log reader tool (Apex sells one) but they're not cheap and there's no free one for SQL 2005. Plus, if you are either in simple recovery or have no backup at all, there's very little chance that they'll recover anything.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

A long shot...

Was your update query some mathematical updation like adding 10 yrs to a date column or something similar. In that case you can reverse it otherwise its just like how Gail pointed out.

I had a similar "accident" where i added 10 yrs to a date instead of 1. I reversed it back by deducting 9 yrs with another update query. 😉

"Keep Trying"

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 3

Hey if you've Full or Bulk-Logged Recovery MOde than chances are you can recover your 100% data without any loss.

For this you should have:

1. Full Backup prior to this incident.

2. Recovery Model should be either Full or Bulk-Logged.

3. If Any Diff Backup which has been taken before this incident since last Full Backup.

4. and Log Backups what so ever you've taken till this incident + a tail log backup immediately after this activity.

Lets assume you executed that update statement at around 2:25 PM and you've log backup scheduled every 15 minutes.

Steps to recover:

1. Arrange a second server

2. Restore your Last Full Backup in Stand-By mode.

3. Restore Diff Backup to it if any in Stand-By mode.

4. Restore all Log Backups with Stand-By mode till 2:15 PM (acc. to our assumption you need to apply all log backups except the one taken after the activity)

5. Copy the table to one another DB (In this way you've data till 2:15 PM intact without any loss)

6. Now if you remember that you executed that statement at 2:25 try applying the last log backup\ tail log backup and put a clause to stop it just at 2:23 PM (obviously in stand-by mode)

7. Now you can again copy the table to another DB (& you've data till 2:23 PM intact without any loss)

8. Now if you want you can go minute by minute to recover more data. 😎

This way you can recover all your data provided you've not discarded any t-log backup. That too without any tool.

🙂

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Sarab (8/15/2009)
For this you should have:
1. Full Backup prior to this incident.

Considering that he said he has no backup, it's not overly likely that he'll be able to do any of this...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 5

0ops :unsure:

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek