MCTS SQL Server 2008 Administration 3 — Questions and Answers
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?
- Restore the full backup only
- Restore full backup, then all differential backups, then all log backups in order (Correct answer)
- Restore the most recent differential backup only
- Restore full backup, skip the corrupted logs, and accept data loss
Correct answer: Restore full backup, then all differential backups, then all log backups in order
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?
- It skips consistency checks during restore
- It leaves the database in a restoring state so additional backups can be applied (Correct answer)
- It restores the database without transaction log recovery
- It prevents users from connecting during restore
Correct answer: It leaves the database in a restoring state so additional backups can be applied
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?
- Database Mirroring with witness
- Log Shipping (Correct answer)
- Replication
- Peer-to-peer transactional replication
Correct answer: Log Shipping
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?
- CPU pressure from parallel queries
- Disk I/O bottleneck causing slow page reads from storage (Correct answer)
- Network latency between client and server
- Excessive blocking from lock contention
Correct answer: Disk I/O bottleneck causing slow page reads from storage
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?
- Detach the database, copy files, reattach
- Use ALTER DATABASE to set files OFFLINE, copy them, then use ALTER DATABASE MODIFY FILE to update paths
- Use backup and restore to the new location (Correct answer)
- Copy files while database is online using Windows Explorer
Correct answer: Use backup and restore to the new location
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.
Question 6: What is the maximum number of CPUs that SQL Server 2008 Standard Edition supports?
- 2
- 4 (Correct answer)
- 8
- 16
Correct answer: 4
SQL Server 2008 Standard Edition supports up to 4 CPU sockets, making it suitable for smaller workloads compared to Enterprise Edition.
Question 7: Which DBCC command is used to check the logical and physical integrity of all objects in a SQL Server database?
- DBCC CHECKALLOC
- DBCC CHECKCATALOG
- DBCC CHECKDB (Correct answer)
- DBCC CHECKTABLE
Correct answer: DBCC CHECKDB
DBCC CHECKDB validates the allocation structures, catalog integrity, and checks every table and indexed view in the specified database.
MCTS SQL Server 2008 Administration 3
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?