MCTS SQL Server 2008 Administration 3

Question 1

You have a database with a FULL recovery model. After a tail-log backup fails due to log file corruption, what is the correct restore sequence?

Accepted Answer

Restore full backup, then all differential backups, then all log backups in order

Answer

In FULL recovery, you must restore the full backup, then the latest differential, then all subsequent log backups in sequence to reach the desired recovery point.

Question 2

What does the NORECOVERY option do when used in a RESTORE statement?

Accepted Answer

It leaves the database in a restoring state so additional backups can be applied

Answer

NORECOVERY leaves the database in a restoring/standby state, allowing additional differential or log backups to be applied before bringing the database online.

Question 3

Which SQL Server 2008 feature allows a secondary database to receive transaction log backups from a primary and be used for read-only reporting?

Accepted Answer

Log Shipping

Answer

Log Shipping automatically sends transaction log backups to one or more secondary servers, which can be configured in STANDBY mode for read-only access.

Question 4

A SQL Server instance is experiencing high PAGEIOLATCH_SH waits. What is the MOST likely root cause?

Accepted Answer

Disk I/O bottleneck causing slow page reads from storage

Answer

PAGEIOLATCH_SH waits indicate threads are waiting to read data pages from disk into the buffer pool, pointing to an I/O subsystem bottleneck.

Question 5

You need to move a user database to a new drive without taking it offline for long. Which technique minimizes downtime?

Accepted Answer

Use backup and restore to the new location

Answer

Backup and restore to the new location with MOVE option allows you to redirect data and log files, minimizing the time the database is unavailable.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS SQL Server 2008 Administration 3