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
You need to have sufficient disk space on the spare system to be able to restore the backup onto. If the production database is
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.