Although BACKUP DATABASE ensures that the database is in a usable state, RESTORE DATABASE does not check for inconsistencies in restored data. Validate the database before a backup to ensure that the database you restore is stable.
The sp_iqcheckdb stored procedure is the interface to the database consistency checker (DBCC), which performs database verification. DBCC has different verification modes that perform increasing degrees of consistency checking. Run sp_iqcheckdb before a backup, or whenever you suspect a problem with the database. On multiplex, run sp_iqcheckdbonly on a write server.
Set the database DBCC_LOG_PROGRESS option to write progress messages to the message file as sp_iqcheckdb executes.
| Verification Mode | Description |
|---|---|
|
Check Database Mode |
Performs an internal consistency check on all IQ indexes and checks that each database block has been allocated correctly. Syntax:
sp_iqcheckdb 'check database' |
|
Verify Database Mode |
Reads all data pages and can detect all types of allocation problems and all types of index inconsistencies. Syntax:
sp_iqcheckdb 'verify database' |
|
Database Allocation Mode |
Checks that each database block is allocated correctly according to the internal physical page mapping structures. Syntax:
sp_iqcheckdb 'allocation database' |