patternsqlMinor
Can a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server?
Viewed 0 times
canfulldbccoperationdifferentcheckdbserversafelyrestoredoffloaded
Problem
We are currently moving towards a 24/7/365 operating model - as such I am investigating our maintenance strategy.
One item I'm having trouble in figuring out is DBCC CHECKDB. I have seen numerous resources report that DBCC can be offloaded by performing a FULL backup, restoring that backup on a separate SQL instance, and running our CHECKDB on that restored copy. I've also found some resources that report this is not safe due to edge-case scenarios.
Is running CHECKDB safe if not run on the production database itself? If not, what parts are safe to run on a restored copy?
One item I'm having trouble in figuring out is DBCC CHECKDB. I have seen numerous resources report that DBCC can be offloaded by performing a FULL backup, restoring that backup on a separate SQL instance, and running our CHECKDB on that restored copy. I've also found some resources that report this is not safe due to edge-case scenarios.
Is running CHECKDB safe if not run on the production database itself? If not, what parts are safe to run on a restored copy?
Solution
SQL Server expert Paul Randal discusses this option in his post CHECKDB From Every Angle: Consistency Checking Options for a VLDB
Use a separate system
This alternative is relatively simple – restore your backup (you are
taking regular backups, right?) on another system and run a full DBCC
CHECKDB on the restored database. This offloads the consistency
checking burden from the production system and also allows you to
check that your backups are valid. There are some drawbacks to this
however:
-
You need to have sufficient disk space on the spare system to be able to restore the backup onto. If the production database is
several
TB, you need the same several TB on the spare box. This equates to a
non-trivial amount of money – initial capital investment plus ongoing
storage management costs. (Hopefully a future release will alleviate
this – while at Microsoft I invented and patented a mechanism for
consistency checking a database in a backup without restoring it.)
-
If the consistency checks find an error, you don’t know for sure that the database is corrupt on the production system. It could be a
problem with the spare box that’s caused the corruption. The only way
to know for sure is to run a consistency check on the production
system. This is a small price to pay though, because most of the time
the consistency checks on the spare system will be ok, so you know the
production database was clean at the time the backup was taken.
Other than the drawbacks that Paul mentions, I'm not aware of any problems with offloading the consistency checks to another system.
I provided an answer to a question - How to conduct integrity test on SQL Server database backup file? where I described the process we use at my shop.
At my shop, we have a 'play' server that we use to automate restores
of the latest FULL/DIFF of the databases we deem 'important'. We use
Windows Task Scheduler to kick off a bat file which has several SQLCMD
steps. After successful restores, we run full DBCC CHECKDB WITH
ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file. Then we
email the output txt files to the database group for evaluation.
This process tests TWO things
One additional note: Corruption can happen anytime. Immediately after a good
Use a separate system
This alternative is relatively simple – restore your backup (you are
taking regular backups, right?) on another system and run a full DBCC
CHECKDB on the restored database. This offloads the consistency
checking burden from the production system and also allows you to
check that your backups are valid. There are some drawbacks to this
however:
-
You need to have sufficient disk space on the spare system to be able to restore the backup onto. If the production database is
several
TB, you need the same several TB on the spare box. This equates to a
non-trivial amount of money – initial capital investment plus ongoing
storage management costs. (Hopefully a future release will alleviate
this – while at Microsoft I invented and patented a mechanism for
consistency checking a database in a backup without restoring it.)
-
If the consistency checks find an error, you don’t know for sure that the database is corrupt on the production system. It could be a
problem with the spare box that’s caused the corruption. The only way
to know for sure is to run a consistency check on the production
system. This is a small price to pay though, because most of the time
the consistency checks on the spare system will be ok, so you know the
production database was clean at the time the backup was taken.
Other than the drawbacks that Paul mentions, I'm not aware of any problems with offloading the consistency checks to another system.
I provided an answer to a question - How to conduct integrity test on SQL Server database backup file? where I described the process we use at my shop.
At my shop, we have a 'play' server that we use to automate restores
of the latest FULL/DIFF of the databases we deem 'important'. We use
Windows Task Scheduler to kick off a bat file which has several SQLCMD
steps. After successful restores, we run full DBCC CHECKDB WITH
ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file. Then we
email the output txt files to the database group for evaluation.
This process tests TWO things
- Can I restore my backups?
- Is the data in the backup structurally intact (DBCC)
One additional note: Corruption can happen anytime. Immediately after a good
DBCC CHECKDB or successful backup, so make attempts to run DBCC consistency checks often.Context
StackExchange Database Administrators Q#240375, answer score: 7
Revisions (0)
No revisions yet.