Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database restore to fix corruption errors? Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 2:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:13 AM
Points: 110, Visits: 701
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.
Post #1372936
Posted Monday, October 15, 2012 3:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,390, Visits: 36,823
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 2008, MVP
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

Post #1372959
Posted Monday, October 15, 2012 3:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 10:13 AM
Points: 110, Visits: 701
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!
Post #1372970
Posted Monday, October 15, 2012 4:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,390, Visits: 36,823
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 2008, MVP
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

Post #1372979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse