Restoring Microsoft SQL Server Databases › Restore Options › Database Consistency Check (DBCC) Options

Database Consistency Check (DBCC) Options

A database consistency check (DBCC) tests the physical and logical consistency of a database. DBCC provides the following options:

• After restore

  Performs DBCC after the restore of the database.

• Before restore

  Performs DBCC before an Online Torn Page Repair restore of the database. (SQL Server 2005 or later, Enterprise Edition only.)

• Do not check indexes

  Checks the database for consistency without checking indexes for user-defined tables.

  Note: The system table indexes are checked regardless of whether you select this option.

• Check only the physical consistency of the database

  Detects torn pages and common hardware failures, but does not check the data against the rules of the database schema. It still checks the integrity of the physical structure of the page and record headers, and the consistency between the page's object ID and index ID. This option is available for Microsoft SQL Server 2000 or later. If this option is selected from the Global Agent Options tab, it is ignored for SQL Server 7.0 databases.

All error messages that are generated during the DBCC are recorded in the Agent for Microsoft SQL Server log file called sqlpagw.log. The log is located in the Backup Agent directory.

Force Restore to use Named Pipes

This option forces the agent to use Named Pipes to return the data to the Microsoft SQL Server. This provides an alternate mechanism for restoring a session if the Virtual Device Interface is not functioning properly. This option is available for Microsoft SQL Server 7.0 and Microsoft SQL Server 2000.

BSBAB--Continue Restore After Checksum Failure-MSSSQLSrvW

This option allows Microsoft SQL Server 2005 to continue processing a restore if it detects an inconsistency between the data and checksums included in the backup.

Miscellaneous

The following are the miscellaneous options you can select from:

Force Restore Over Existing Files

• Force Restore Over Existing Files and Databases

  Lets Microsoft SQL Server overwrite files it does not recognize as part of the database it is restoring. Use this option only if you receive a message from Microsoft SQL Server prompting you to use the With Replace option.

  Microsoft SQL Server supports this option for database restore and file or FileGroup restore operations.

  Important! Microsoft SQL Server 2005 will refuse by default to overwrite an online database using the Full or Bulk-Logged Recovery Model. Instead, it produces an error message saying to either take the database offline by performing a Log Tail backup, or restore with the "WITH REPLACE" option. Selecting this option applies the "WITH REPLACE" option to the restore, and forces SQL Server to overwrite the existing database.

Restricted User Access After Restore Option

• Restricted User Access After Restore Option

  Restricts access to a newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In Microsoft SQL Server 2000 and Microsoft SQL Server 2005, Restricted_User replaces the DBO_Only option from Microsoft SQL Server 7.0. This option requires the Leave database operational, no additional transaction logs can be restored option.