You are a professional level SQL Sever 2008 Database Administrator.
All data changes are implemented through stored procedures, and only the INSERT, UPDATE, or DELETE statements are utilized by the procedures. A backup strategy should be implemented.
The business requirements listed below should be satisfied:
Point-in-time recovery for failure is supported by the backup strategy at any time of day.
The least amount of disk space should be utilized by the transaction log.
Which action should you perform to finish the task?

• A. To finish the task, the simple-recovery model along with differential backups should be utilized.
• B. To finish the task, the full-recovery model along with transaction log backups should be utilized.
• C. To finish the task, hourly database snapshots should be utilized.
• D. To finish the task, the full-recovery model along with differential backups should be utilized.

Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
It is owned by a user named SalesUser.
Another user named MarketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permissions necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Perform the following tasks: Set Select on Customer.
• B. Set only Take Ownership on Customer and Order.
• C. Set only Execute on SetCustomerStatus.
• D. Perform the following tasks: Set Execute on SetCustomerStatus.

You are a professional level SQL Sever 2008 Database Administrator.
A large database is hosted by the instance. The company utilizes the following backup strategy for the
database.
First, the performance of a full database backup is implemented at 02:00 hr every Monday.
Secondly, the performance of a differential backup is implemented at 23:00 hr every day.
Thirdly, the performance of a transaction log backup is implemented on an hourly basis.
A reboot is caused on the SQL Server 2008 server by a power failure at 09:15 hr, Thursday.
Fifteen minutes after the reboot of the server, it is reported that certain queries that access customer data
cannot be executed. You find that the customer data remains unvaried after the power failure.
When the DBCC CHECKDB command is executed on the database, the following error message is
received.
Object ID 2814307832,
index ID 2, partition ID 83127819437122157,
alloc unit ID 82134587923221126 (type In-row data): Page (3421:169) could not be processed.
See other errors for details.
Table error: Object ID 2814307832, index ID 2, partition ID 83127819437122157,
alloc unit ID 82134587923221126 (type In-row data), page (3421:169).
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
Values are 16928422 and -8.CHECKDB found 0 allocation errors and 2 consistency errors in table
'tbl_Customer' (object ID 2814307832).
When the sp_help 'tbl_customer' stored procedure is executed, the following result set is received:
index_name index_description
PK clustered located on PRIMARY NCIX nonclustered located on PRIMARY
Since you are the technical support, you are required to make sure that the data should be accessible in the shortest time. And the impact on users should be cut to the least.
Which action will you perform to finish the task?

• A. To finish the task, the NCIX index should be dropped and recreated.
• B. To finish the task, the latest full database backup should be recovered. And then, the latest differential backup should be recovered. At last, all transaction log backups should be recovered from the latest differential backup.
• C. To finish the task, the PK index should be dropped and recreated.
• D. To finish the task, the latest full database backup should be recovered. And all transaction log backups should be recovered from the latest full database backup.

You administer a SQL Server 2008 instance that hosts a large database.
The following backup strategy is used for the database:
A full database backup is performed at 02:00 hr every Monday.
A differential backup is performed at 23:00 hr every day.
A transaction log backup is performed on an hourly basis.
A power failure on Thursday causes the SQL Server 2008 server to restart at 09:15 hr.
Fifteen minutes after the server restarts, the users report that they are unable to execute certain queries
that access
customer data. You discover that the customer data is unmodified after the power failure.
When you execute the DBCC CHECKDB command on the database, you receive the following error
message:
Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126
(type In-row data): Page (3421:169) could not be processed. See other errors for d etails.
Table error: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID
82134587923221126 (type In-row data), page (3421:169). Test (IS_OFF (BUF_IOERR, pBUF- > bstat))
failed. Values are 16928422 and -8.
CHECKDB found 0 allocatio n errors and 2 consistency errors in table 'tbl_Customer' (object ID
2814307832).
When you execute the sp_help 'tbl_customer' stored procedure you receive the following result set:
index_name index_description PK clustered located on PRIMARY NCIX nonclustered located on PRIMARY
You need to ensure that the data is available as quickly as possible with minimal effect on users.
What should you do?

• A. Restore the latest full database backup. Restore the latest differential backup. Restore all transaction log backups from the latest differential backup.
• B. Restore the latest full database backup. Restore all transaction log backups from the latest full database backup.
• C. Drop and recreate the PK index.
• D. Drop and recreate the NCIX index.

You administer a SQL Server 2008 infrastructure.
Your company requires capacity planning information.
You need to design a long-term infrastructure monitoring strategy.
Which two tasks should you include in your design? (Each correct answer presents part of the solution. Choose two.)

• A. Clear the system log and the application log every hour.
• B. Backup all databases every day.
• C. Review system monitor counters on a regular basis.
• D. Baseline the system before you deploy production databases.
• E. Create a maintenance plan that rebuilds indexes every week.