How to automate DBCC CHECKDB after a database restore

Why running DBCC CHECKDB?

DBCC CHECKDB checks the logical and physical integrity of all the objects in a database and provides information of any corruption.

As performing DBCC CHECKDB is a resource exhaustive task it is recommended to run it on a production server when there is as less traffic as possible, or even better, as one of the ways to speed up the DBCC CHECKDB process, is to transfer the work to a different server by automating a process and run CHECKDB after a database restore. As a backup process is a copy of a database and a restored database will be exactly the same as an online database therefore if there were any inconsistencies or issues they will be in the backup, and found in a restore. By using this approach both restores will be tested and backups verified without any impact on a production database.

Running DBCC CHECKDB after a restore can also be a good practice if a backup’s quality is unknown.

In this article we will describe how to automate a DBCC CHECKDB after a database restore by automating DBCC CHECKDB after a database restore by using a SQL Server Agent job, Maintenance Plans, and ApexSQL Backup and explain some of the DBCC CHECKDB options available in ApexSQL Backup.

How to automate DBCC CHECKDB after a database restore by using a SQL Server Agent job

To automate DBCC CHECKDB after a database restore by using SQL Server Agent:

  1. Select a New Job from the SQL Server Agent node ➜ Jobs
  2. Create a first step and specify a database backup to restore by using the following script:

    RESTORE DATABASE AdventureWorks2014CheckDB 
    FROM DISK = 'E:\EncBackup\AdventureWorks2014_Full_20150612_1916.bak' 
    WITH RECOVERY
    GO
    

  3. Add a second step to perform DBCC CHECKDB on a restored database by adding the following T-SQL script:

    DBCC CHECKDB (AdventureWorks2014CheckDB)
    

    To use different DBCC CHECKDB options use the following T-SQL scripts:

    To check column values and to check are there column values that are out of range:

    DBCC CHECKDB (AdventureWorks2014CheckDB) WITH DATA_PURITY
    

    To subdue unnecessary informational messages:

    DBCC CHECKDB (AdventureWorks2014CheckDB) WITH NO_INFOMSGS
    

    To check indexed views, spatial, and XML indexes:

    DBCC CHECKDB (AdventureWorks2014CheckDB) WITH EXTENDED_LOGICAL_CHECKS
    

    To check only the physical structure of a database:

    DBCC CHECKDB (AdventureWorks2014CheckDB) WITH PHYSICAL_ONLY
    

    To enforce table-shared locking:

    DBCC CHECKDB (AdventureWorks2014CheckDB) WITH TABLOCK
    

    These options can be combined except the WITH PHYSICAL_ONLY option that cannot be combined with some of the options, which will be explained later in this article.

  4. Schedule a job to a desired frequency of executing.

Although relatively easy to set up using a SQL Server Agent job for these operations requires writing T-SQL scripts and a knowledge of DBCC CHECKDB options syntax as well as a knowledge of each option functionality and possibilities of combining.

How to automate DBCC CHECKDB after a database restore by using Maintenance Plan

Maintenance Plans can also be used to perform this action. Although the Maintenance Plans toolbar doesn’t contain a restore task the T-SQL task can be used.

To automate DBCC CHECKDB after a database restore by using Maintenance Plans:

  1. From the Management node in the Object Explorer pane, under Maintenance Plans select the New Maintenance Plan
  2. In the New Maintenance Plan name a new plan
  3. From the Maintenance Plan Tasks toolbar select Execute T-SQL Statement Task

  4. Double slick the task and paste a T-SQL script:

  5. Add Check Database Integrity Task from the Maintenance Plan Tasks toolbar:

  6. Double click on a task and select a database to run the task against:

  7. When a maintenance plan is set up schedule it as a SQL Server Agent job:

Downside of this approach besides being more complicated to set up is that currently Check Database Integrity Task offers only indexes check as an additional option and performs a full DBCC CHECKDB check.

More options will be added in a SQL Server 2016 version.

How to automate DBCC CHECKDB after a database restore by using ApexSQL Backup

ApexSQL Backup a SQL backup and restore manager, offers an option to automatically perform DBCC CHECKDB command during a restore and allow user to choose between options to include in a check.

ApexSQL Backup enables the user to automate the process in just a few clicks without the need to write a T-SQL script and additional commands for additional options explained below as it offers them out of the box.

ApexSQL Backup also automatically disables options that cannot be combined if one of them is selected thus preventing the user to experience errors, for example:

Msg 2532, Level 16, State 2, Line 8
One or more WITH options specified are not valid for this command.

To automate DBCC CHECKDB after a database restore by using ApexSQL Backup:

  1. Select the Restore option from the ApexSQL Backup menu
  2. In the Restore wizard dialog select a server to connect to and enter a database name:

  3. Specify and exact backup file to restore or use the Folder scan option:

  4. In the Options window of the Restore wizard select the Run database integrity check after restore (DBCC CHECKDB) option, and select the desired additional options:

Choosing the options

The WITH PHYSICAL_ONLY DBCC CHECKDB option

Selecting the WITH PHYSICAL_ONLY option forces DBCC CHECKDB to skip the consistency checks that are costly for the CPU providing a low overhead check of the physical consistency allowing that DBCC CHECKDB process runs significantly faster. The PHYSICAL_ONLY option switches on the NO_INFOMSGS option.

This option turns off other options of DBCC CHECKDB (the DATA_PURITY option and any repair options) that could be useful for more thorough corruption inspection.

The WITH DATA_PURITY DBCC CHECKDB option

SQL Server 2005 introduced the DATA_PURITY option that is added to the DBCC CHECKDB command. This option checks for the column values and detects the issues if a column values are out of range or invalid. For databases created in versions 2005 and older the DATA_PYRITY doesn’t have to be specified as it is checked automatically. This doesn’t apply on databases that are upgraded from an older versions and in those cases this option should be specified when DBCC CHECKDB is run.

The WITH EXTENDED_LOGICAL_CHECKS DBCC CHECKDB option

This option is used to perform consistency checks on indexed views, spatial indexes, and XML indexes. To perform consistency check on this types of indexes this option has to be added because this check is not performed by default since SQL Server 2008.

When checking an indexed view consistency DBCC CHECKDB checks does the does the indexed view match the view definition and reports is some rows are missing or if there are additional rows. Consistency checks of the XML indexes and spatial indexes is similar to a consistency check of the indexed views with small differences.

Additional rows for all three types of indexes are indicated with as 8907 errors, and the missing rows are indicated with the 8908 errors.

These checks are very time and space expensive to run and they depend on the indexes definitions, because the more complex an index definition is the more time is required to generate a copy of an index in the tempdb database, and the more space it will consume in the tempdb database.

The WITH TABLOCK DBCC CHECKDB option

DBCC CHECKDB uses the schema lock meaning that while the data is read other transactions can’t modify that data but the locked data can be read. The shared lock is released after a process of reading the locked data if finished.

If the WITH TABLOCK option is selected it will reduce the locking level and force DBCC CHECKDB to use the table-shared locking.

The WITH NO_IFOMSGS DBCC CHECKDB option

When performing DBCC CHECKDB it provides the user with a substantial number information in the output. By selecting the NO_INFOMSGS option will reduce the amount of information to only errors that are found and allow the user to identify reported error more quickly and easily.

  1. After setting up the desired options in the Options window of the Restore wizard dialog schedule a job to a desired frequency of occurring and optionally select to be notified by an email:

  2. Submit a schedule by clicking the Next button:

  3. Finish the process by selecting the Finish button:

June 22, 2015