Backing up a database is useful only if the database is internally consistent.
Backup always makes sure that the database is in a usable state before proceeding. However, validating a database before you perform a backup is a good idea, to ensure that the database you restore is stable. The restore program does not check for inconsistencies in the restored data, since the database may not even exist.
sp_iqcheckdb 'check database'
The sp_iqcheckdb stored procedure, in conjunction with server startup switches, is the interface to the IQ Database Consistency Checker (DBCC).
DBCC has different verification modes that perform increasing amounts of consistency checking. There are three modes for checking database consistency and one for resetting allocation maps. Each mode checks all database objects, if you specify 'database' as the target in the sp_iqcheckdb command string. Individual tables and indexes can also be specified in the command string. If you specify individual table names, all indexes within those tables are also checked.
The database option DBCC_LOG_PROGRESS instructs sp_iqcheckdb to send progress messages to the IQ message file. These messages allow you to follow the progress of the sp_iqcheckdb procedure as it executes.
You should run sp_iqcheckdb before or after backup, and whenever you suspect a problem with the database.
When you run sp_iqcheckdb on an entire database, sp_iqcheckdb reads every database page in use.
This procedure consumes most of the database server's time, so that the I/O is as efficient as possible. Any other concurrent activities on the system run more slowly than usual. The CPU utilization of DBCC can be limited by specifying the sp_iqcheckdb parameter resources resource-percent, which controls the number of threads with respect to the number of CPUs.
If other users are active when you run sp_iqcheckdb, the results you see reflect only what your transaction sees.