Administering a SQL Database Infrastructure — Question 232

A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups.
DB1 experiences a failure. You must restore FG1 and then FG2.
You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online.
What should you specify when you run the recovery command?

Answer options

• A. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
• B. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
• C. the WITH RECOVERY clause for both FG1 and FG2
• D. the WITH NORECOVERY clause for both FG1 and FG2

Correct answer: A

Explanation

The correct answer is A because using the WITH NORECOVERY clause for FG1 allows you to restore FG2 later while keeping the database in a RECOVERING state. If you had used the WITH RECOVERY clause for FG1, the restoration process would complete, bringing the database online prematurely, which is not desired until FG2 is also restored. The other options do not satisfy the requirement of keeping the database in the RECOVERING state until both filegroups are restored.