SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database restore to fix corruption errors?


Database restore to fix corruption errors?

Author
Message
ahenderson
ahenderson
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 754
Hi, I read in several places that it is recommended to restore a database backup instead of running REPAIR_ALLOW_DATA_LOSS to fix corruption issues. I'm just wondering how this could be an acceptable solution.

Lets say you run a full CHECKDB once a week and spot corruption, but you find the corruption happened 6 days prior so the nightly backups going back 6 days would all have the corrupt pages. Wouldn't you have to restore an old database backup and lose almost a week of data? Is this really what is recommended or am I missing something?

I've read about using a old backup to restore over individual corrupt pages which would make more sense. However, what is the best way to determine how far back you need to go to find the latest backup before the corruption happened? How can you determine WHEN the corruption happened?

Thanks.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87685 Visits: 45272
ahenderson (10/15/2012)
Lets say you run a full CHECKDB once a week and spot corruption, but you find the corruption happened 6 days prior so the nightly backups going back 6 days would all have the corrupt pages. Wouldn't you have to restore an old database backup and lose almost a week of data? Is this really what is recommended or am I missing something?


Yes, it's really what's recommended and what you're missing is log backups.
If you're not retaining log backups for 7 days (in your example), then you're not running checkDB often enough or you're not retaining backups long enough

I've read about using a old backup to restore over individual corrupt pages which would make more sense. However, what is the best way to determine how far back you need to go to find the latest backup before the corruption happened? How can you determine WHEN the corruption happened?


Sometime between the last checkDB that ran clean and the first that errored out. Which is why you need to be running it regularly.
To restore pages, you still need an unbroken chain of log backups from the backup used to current. Otherwise you can't bring those pages out of the restoring state.

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


ahenderson
ahenderson
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 754
I see, that make sense. :-)

The log would reapply the transactions but not the corrupt pages themselves. I'll be sure we have an unbroken chain going back that far enough. Good to know, thanks!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87685 Visits: 45272
Exactly. Corruption is 99% IO subsystem errors, and a borked IO subsystem isn't polite enough to log the mangling it's doing of the data file. Also the reason that in a mirroring setup if the principal gets corrupt there's an extremely good chance the mirror DB is fine.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search