You have SQL Server on Azure virtual machines in an availability group. You have a database named DB1 that is NOT in the availability group. You create a full database backup of DB1. You need to add DB1 to the availability group. Which restore option should you use on the secondary replica?

To manually add a database to an Always On availability group, the database on the secondary replica must be restored into a state that allows the availability group to start synchronizing it. The RESTORE WITH NORECOVERY option leaves the database in a RESTORING state. This specific state is required so that subsequent transaction log backups can be applied and, more importantly, so that the availability group can take over synchronization from the primary replica once the database is joined. The database remains inaccessible until it is synchronized and brought online by the availability group.

A. Restore with Recovery: This option finalizes the database recovery process, brings the database online, and makes it accessible. A database in this state cannot be joined to an availability group as a secondary because it cannot receive and apply log records from the primary.
C. Restore with Standby: This option brings the database to a read-only state and allows for additional transaction log restores. While used in scenarios like log shipping to allow for reporting, it is not the standard or required state for preparing a database to join an availability group. The NORECOVERY option is the correct and direct method.

1. Microsoft Docs

Manually prepare a secondary database for an availability group (SQL Server)

Last Updated: 02/17/2023.

Section: "To prepare a secondary database"

Paragraph/Step 3: "On the instance of SQL Server that hosts the secondary replica

restore the full database backup of the primary database

using RESTORE WITH NORECOVERY... If you need to restore any further backups

they must also be restored using the WITH NORECOVERY option." This directly confirms that NORECOVERY is the required option.

2. Microsoft Docs

RESTORE Statements - Arguments (Transact-SQL)

Last Updated: 03/04/2024.

Section: "{ RECOVERY | NORECOVERY | STANDBY = standbyfilename }"

Paragraph on NORECOVERY: "Instructs RESTORE to not roll back any uncommitted transactions. The NORECOVERY option allows a subsequent RESTORE LOG operation to be applied to the database. The database is not usable in this state." This explains the technical state that NORECOVERY creates

which is essential for AG seeding.

3. Microsoft Docs

Start Data Movement on an Always On Secondary Database (SQL Server)

Last Updated: 02/17/2023.

Section: "Prerequisites"

Paragraph: "Your secondary databases must be in the RESTORING state. To put a secondary database into the RESTORING state

restore the database backups using the WITH NORECOVERY Transact-SQL option." This reinforces that the RESTORING state

achieved via WITH NORECOVERY

is a prerequisite.