In an earlier, post I talked about doing backups and also making sure they are being done with data that has been verified as healthy. We want to avoid the scenario of garbage in leads to garbage out. As stated in that earlier article:
You should be running DBCC CheckDB regularly. Daily if your system can handle it, but at least weekly. You don’t need to check for the last good DBCC CheckDB if you are running regular CheckDB. For backups, you should be using the CHECKSUM option of the backup command.
So how do we get there and what tool can we use? The answer is simple we can use the built in tool DBCC CHECKDB.
DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database. If DBCC CHECKDB is ran on a database please note that you do not need to run DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG since DBCC CHECKDB includes all the three command. Usage of these built-in DBCC commands is listed below.
DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.
Along with above three DBCC commands it also runs following two tasks to check the validity of the database both physical as well logical. If the DBCC check has returned any errors on a production database, the best solution is to RESTORE your DATABASE from BACKUP. If you’re unable to schedule 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.