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