You are the SQL administrator for your company. A SQL Server 2008 instance named SQL_Prod containsseveral databases your company uses on a daily basis.
You decide to create a maintenance plan to meet the following objectives:
? The database structure of all databases and indexes on SQL_Prod must be verified.
? All user databases must be backed up before the structure of the databases is verified.
? You must be notified when the maintenance plan has completed.
What should you do?

A.
Create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All databases option selected
? the Check Database Integrity Task with the All user databases and Include indexes options selected
? the Notify Operator Task configured to notify you when the plan completes

B.
Create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All databases option selected
? the Check Database Integrity Task with the All user databases option selected
? the Rebuild Index Task with the All user databases option selected
? the Notify Operator Task configured to notify you when the plan completes

C.
Create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All user databases option selected
? the Check Database Integrity Task with the All databases option selected
? the Rebuild Index Task with the All databases option selected
? the Notify Operator Task configured to notify you when the plan completes

D.
Create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All user databases option selected
? the Check Database Integrity Task with the All databases and Include indexes options selected
? the Notify Operator Task configured to notify you when the plan completes

Explanation:

You should create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All user databases option selected
? the Check Database Integrity Task with the All databases and Include indexes options selected
? the Notify Operator Task configured to notify you when the plan completes
The Back Up Database Task will back up the databases you select. In this scenario, you must select the Alluser databases option because you only wanted to back up the user databases. The Check Database IntegrityTask will verify the structural integrity of the databases and indexes. The All databases option must be selectedbecause you wanted to verify the structural integrity of all the databases. The Include indexes option must alsobe selected because you want to verify the structural integrity of the indexes. The Notify Operator Task must runlast because you want the maintenance plan to notify you when it completes. You should not create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All databases option selected
? the Check Database Integrity Task with the All user databases and Include indexes options selected
? the Notify Operator Task configured to contact you when the plan completes
The Back Up Database Task should only be run on the user databases, not all databases. In addition, the Check Database Integrity Task should be run on all databases, not just the user databases. You should not create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All databases option selected
? the Check Database Integrity Task with the All user databases option selected
? the Rebuild Index Task with the All user databases option selected
? the Notify Operator Task configured to contact you when the plan completes
The Back Up Database Task should only be run on the user databases, not all databases. In addition, the Check Database Integrity Task should be run on all databases, not just the user databases. Finally, you shouldnot run the Rebuild Index Task because it rebuilds indexes but does not verify the structural integrity of indexes.You need to enable the Include indexes option of the Check Database Integrity Task to accomplish that. You should not create a maintenance plan that includes tasks, as follows:
? the Back Up Database Task with the All user databases option selected
? the Check Database Integrity Task with the All databases option selected
? the Rebuild Index Task with the All databases option selected
? the Notify Operator Task configured to contact you when the plan completes
You should not run the Rebuild Index Task
because it rebuilds indexes but does not verify the structural integrityof indexes. You need to enable the Include indexes option of the Check Database Integrity Task to accomplishthat.

Objective:
Maintaining a SQL Server Database

Sub-Objective:
Maintain a database by using maintenance plans.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Integration Services > Development > Designing andImplementing Packages > Understanding the Components of an Integration Services Package > Control FlowElements > Integration Services Tasks > Maintenance Tasks