Run DBCC CHECKDB before a backup

ADMINISTRATOR

We recommend that you perform a consistency check using SQL Server DBCC CHECKDB before performing an online full database backup.

DBCC CHECKDB (from the Microsoft MSDN Library), checks the logical and physical integrity of all objects in a database. Data corruption can cause all kinds of issues within a database. DBCC CHECKDB warns you about corruption so you can fix it before it gets worse. See the Microsoft SQL Server documentation for more information about DBCC CHECKDB.

On commodity hardware with a good I/O subsystem, I/O throughputs in the range of 100-150 GB/h can be achieved. Given such I/O throughputs, running DBCC CHECKDB on a production system is not always practical, resulting in some customers choosing not to run DBCC CHECKDB.

Although hardware and software are more reliable every year, physical corruption can still happen (for example a catastrophic power outage without battery backup for hardware components). Physical damage to connections and hardware components is always a possibility. If a critical failure of any kind occurs, sometimes the only way to restore data is to revert to a previous database backup to restore the FactoryLogix Database, then apply all the transaction logs up to the most recent one.

To detect physical problems at an early stage, ensure your backup method is reliable, and minimize the impact of potential physical corruptions, consider the following three major measures:

• Run DBCC CHECKDB on a regular basis on a sandbox system that has a restored image of the production environment.
  
  On such a system, time and resource consumption of DBCC CHECKDB won't affect production users.
  

• Run DBCC CHECKDB on a regular basis on the production system, but only during a scheduled maintenance window.

• Test restoring the FactoryLogix Database from an online backup or a differential and transaction log backup. 

  Note:

  The fact that a backup is on tape doesn't necessarily mean that it's consistent on tape or can be read from tape. Tape hardware and tape cassettes can fail. Simply having a backup in a vault don't guarantee the ability to restore data successfully in case of a disaster—the backup must also be readable.

• For large databases with terabytes of volume, keep a second copy of the database at its most recent status using either log shipping or database mirroring.
  
  Both of these high-availability methods will de-couple hardware components and may provide a physical, consistent image of the production database at a secondary site.