October 15, 2012 at 2:27 pm
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.
October 15, 2012 at 3:08 pm
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
October 15, 2012 at 3:35 pm
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!
October 15, 2012 at 4:07 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply