Sql-server – Can a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server

dbcc-checkdbsql server

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?

Best Answer

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

  • 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.

Related Question