DP-300 Question #182: Real Exam Question with Answer & Explanation

Question

Hotspot Question You have two Azure virtual machines named VM1 and VM2 that run Windows Server 2019. VM1 and VM2 each host a default Microsoft SQL Server 2019 instance. VM1 contains a database named DB1 that is backed up to a file named D:\DB1.bak. You plan to deploy an Always On availability group that will have the following configurations: - VM1 will host the primary replica of DB1. - VM2 will host a secondary replica of DB1. You need to prepare the secondary database on VM2 for the availability group. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. Answer:

Options

• __typehotspot
• variantdropdown

Explanation

This question tests knowledge of preparing a secondary database for an Always On Availability Group in SQL Server by restoring a database backup with the correct restore options on the secondary replica (VM2).

Approach. To prepare the secondary database on VM2, you must restore DB1 from the backup file (D:\DB1.bak) using RESTORE DATABASE with two critical options: WITH NORECOVERY and MOVE if needed. The key Transact-SQL statement is: RESTORE DATABASE DB1 FROM DISK = 'D:\DB1.bak' WITH NORECOVERY. The WITH NORECOVERY option is essential because it leaves the database in a restoring state, which is required for Always On Availability Groups - it allows subsequent transaction log backups to be applied and allows the database to join the availability group. Using WITH RECOVERY would bring the database online and prevent it from being added to an AG. Additionally, a transaction log backup must be taken on VM1 and restored on VM2 WITH NORECOVERY before joining the AG, unless using automatic seeding.

Concept tested. Preparing a secondary replica database for Always On Availability Groups requires restoring the full database backup (and any log backups) using WITH NORECOVERY, which leaves the database in a non-accessible restoring state so it can receive log shipping and eventually join the availability group.

Reference. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server

No community discussion yet for this question.