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

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

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

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