You are the database administrator for your company. You maintain a production database named Products onyour company’s server. There are approximately 35 objects in the Products database. Your Products database has crashed due to a hardware failure. You were able to copy the .mdf and .ldf files ofthis database from the hard disks onto a different server. You have attached these files to a new server instanceand named the new database Products2 . You want to perform the following actions:
? Check the integrity of all objects in the newly created database.
? Minimize the time required to perform the integrity check.
? Repair errors without the risk of data loss.
? Suppress any informational messages displayed while performing the integrity check.
However, repairing errors without the risk of data loss is more important than reducing the time to perform theoperation or suppressing informational messages.
Which statement will you issue to achieve the stated objectives?

A.
DBCC CHECKDB WITH NO_INFOMSGS;

B.
DBCC CHECKDB(‘Products2’, NOINDEX, REPAIR_FAST) WITH NO_INFOMSGS;

C.
DBCC CHECKDB(‘Products2’, NOINDEX, REPAIR_REBUILD) WITH NO_INFOMSGS;

D.
DBCC CHECKDB(‘Products2’, REPAIR_REBUILD) WITH NO_INFOMSGS;

Explanation:

You should issue the following statement:
DBCC CHECKDB(‘Products2’, REPAIR_REBUILD) WITH NO_INFOMSGS;

This statement fulfills two of the three requirements of this scenario. The DBCC CHECKDB statement checks theallocation, logical integrity, and physical integrity of all the objects in the specified database. In this scenario, thefollowing arguments are used in this statement:
? NOINDEX : Specifies that intensive checks for nonclustered indexes are not performed. Using NOINDEX reduces the time required to perform the integrity checks. In this scenario, the time required must beminimized. However, this is not the most important requirement. You cannot use this option with any repairoptions.
? REPAIR_REBUILD : Specifies that errors will be repaired and ensures that there is no loss of data in therepair process. In this scenario, this was the most important requirement. Therefore, you should includethis argument.
? NO_INFOMSGS : Specifies that no informational messages are displayed.The database name can be specified by using the database_name argument. If the database name is notspecified, the current database will be used. You should not issue the following statement: DBCC CHECKDB WITH NO_INFOMSGS;
This statement does not specify any clause to reduce the time required by the integrity check. In addition, the REPAIR_REBUILD argument is not used to ensure that the errors reported are repaired without incurring anyloss of data. You should not issue the following statement:
DBCC CHECKDB(‘Products2’, NOINDEX, REPAIR_FAST) WITH NO_INFOMSGS;
This statement does not fulfill the objectives specified in this scenario. To perform repairs without the risk of dataloss, you should use the REPAIR_REBUILD
argument. You cannot use both NOINDEX and REPAIR_FAST in asingle DBCC CHECKDB statement. You should not issue the following statement:
DBCC CHECKDB(‘Products2’, NOINDEX, REPAIR_REBUILD) WITH NO_INFOMSGS;
You cannot use both NOINDEX and REPAIR_REBUILD in a single DBCC CHECKDB statement.

Objective:
Maintaining a SQL Server Database

Sub-Objective:
Maintain database integrity.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > DBCC (Transact-SQL) > DBCC CHECKDB (Transact-SQL)