6

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

Improve this question
2
  • Very good question. I have only ever done this when I perform a manual, copy backup before making a change.
    – Robert Kaucher
    Oct 5, 2010 at 20:27
  • @Robert So does that mean you don't do routine DBCC checks? Or that you simply don't do them on prod servers?
    – BradC
    Oct 5, 2010 at 20:28

4 Answers 4

Reset to default
3

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.

Improve this answer
2
  • Awesome idea, and one that @PaulRandal himself recommends. In my case, though, not really practical for thousands of databases across hundreds of servers.
    – BradC
    Oct 5, 2010 at 22:52
  • I guess that would make things a little more difficult. In that case I would agree with Simon above, as long as your are doing regularly scheduled CHECKDB maintenance, you should be fine. If all of your databases are small enough to fit in a CHECKDB in time wise before a BACKUP, that would be ideal, but based on what you described, that's probably not reasonable.
    – SQL3D
    Oct 5, 2010 at 23:22
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.

Improve this answer
1

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.

Improve this answer
0

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.

Improve this answer
1
  • I suppose that depends on the type of issue found by CheckDB, anywhere from a quick REPAIR to a restore from backup. Most production DBs are in FULL RECOVERY, so applying tran log backups is on the table as well.
    – BradC
    Oct 5, 2010 at 22:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .