An integral part of any maintenance plan (and arguably the most important part) is taking frequent backups of the database. A set of Transact SQL commands, collectively known as DBCC commands, includes the CHECKDB function that can be used to verify the integrity of a database. SQL Server also includes the option to Check Database Integrity as part of a Maintenance Plan. This option causes certain DBCC commands to be executed against the database.
Check the database integrity on a regular basis (for example, daily, weekly, or monthly). Frequency is dependent on the available maintenance window.
Check the database integrity immediately prior to backing up a database to ensure you are not overwriting your last good backup with one that contains errors.
An integrity check can be executed against a copy of the database restored to a separate instance, to reduce user impact. However, this may not validate the server or hardware resources being used by the production instance, such as storage resources. If this strategy is used, it is still recommended to periodically execute the integrity check against the active production instance.
In accordance with Microsoft SQL Server Books Online, it is recommended that, if you find corruption in your database, you should first try to resolve the corruption by running DBCC CHECKDB with the REPAIR_FAST and/or REPAIR/REBUILD parameters. If these options do not fix the corruption, it is recommended that you restore your last known good backup. If you are not certain when the last good backup was performed, you will need to restore and run DBCC CHECKDB on each backup until you find one that has no corruption.
If an integrity check identifies corruption and returns a minimum repair level of repair_allow_data_loss, do not execute the command and instead restore to the most recent backup with no corruption present.
Please refer to Microsoft SQL Server Books Online for additional DBCC statements that can be run against a SQL Server database.