Home / server / Questions / 187869
In Process
BradC
Asked: 2010-10-06 12:00:58 +0800 CST

Should I run DBCC checkdb before full backups? Or after?

  • 772

We have a mix of SQL 2000, 2005, and 2008 servers, and we've always run a DBCC CHECKDB nightly just before the full backups, under the theory that you want to make sure the DB is in good shape before you back it up. (Obviously, full verification of backups can only be done via test restores, but thats a slightly different topic.)

Assuming that I can't offload the DBCCs to a backup server or something (which would be ideal), is DBCC CHECKDB followed by FULL BACKUP the best sequence?

The only "best practices" document I found that discuss this was a Best Practices for SQL Server Maintenance for SAP from 2006 I found on TechNet:

Ideally, a consistency check using DBCC CHECKDB sould be run before performing an online database backup.

Is this advice correct? Is it correct for all versions of SQL?

(In case this helps, part of the motivation for asking this is that the DBCC runtime seems to vary a fair amount from night to night, so we can't rely on exactly when the backups will be complete, which makes scheduling our tape archive jobs difficult. Also, if maintenance runs long and has to be cancelled for any reason, I'd much rather that the backup be reliably complete than the DBCC.)

sql-server backup checkdb dbcc

4 Answers

  1. Another thing that you might consider, is if you have another server (Dev or otherwise) that you can use to test restore your backup files, you might want to do it on there. So RESTORE DATABASE and then DBCC CHECKDB. That way, not only are you validating that your backup files are good, but you are validating the databases are good without affecting production.

    We test restore all our backups on a weekly basis to another server and then run the CHECKDB on them.

    • 3

  2. Here's my take...

    In terms of recoverability it doesn't matter exactly when you run CHECKDB, but it MIGHT help your confidence about whether your backup is good or not.

    Say your database becomes corrupted.

    When your pre-backup DBCC CHECKDB fails, you would know your subsequent backup is possibly no good.

    Running backup first, CHECKDB after, you'd be VERY slightly less certain whether you had a good or bad backup (there's the possibility the corruption could have happened between backup and CHECKDB). Does this matter to you?

    I'd say as long as you run CHECKDB regularly, it doesn't matter exactly when. And as you mentioned there's no substitue for regularly testing restores.

    • 2

  3. If you're unable to fit a full DBCC CHECKDB in a maintenance window, you can add WITH CHECKSUM to your backup routines, and do full CHECKDB's at a different time (SQL 2005 and later).

    Note that a BACKUP [...] WITH CHECKSUM does not replace DBCC CHECKDB. Paul Randal has more details here.

    • 1

  4. It would also be helpful to understand what your recovery strategy is if/when you determine you have data corruption. If you intend to restore to a point of failure then running checkdb prior to a backup can save you time and lost data.

    • 0