Mount databases into SQL AlwaysOn Availability Groups

Disable comment:

Mount to a SQL Server AAG using a snapshot pool

This approach is the fastest way to create a new SQL Server AlwaysOn Availability group for short-term use. The mounted application runs in the snapshot pool, and achieves the same performance as data captured in the snapshot pool.

  1. Mount the databases being added to the SQL availability group to each node in the AG, one node at a time. See Mount a SQL Server database.

  2. Select the required host.

  3. Select the SQL instance.

  4. Specify the roll-forward time, being sure to specify the same time during mounts to each node.

  5. Under Advanced Options, deselect Recover Database After Restore.

  6. Enter a name, or a prefix or suffix. Use the same name, prefix, or suffix on all nodes.

  7. Repeat the process for each availability group member.

  8. Once all databases are mounted as virtual databases on each availability group node, using SQL, select the SQL instance that is the primary and for each database recover it:

    `recover database (name) with recovery`

  9. Keep the secondary database copies in restoring state.

  10. When the master database has been restored, using SQL, create the new availability group and join the primary and secondary databases as described in Add virtual databases to a new SQL Server AlwaysOn Availability group.

Clone to a SQL Server availability group independently from the snapshot pool

To clone to a SQL AlwaysOn Availability group that resides independently from the snapshot pool, perform a clone operation for the databases on each node in the SQL AlwaysOn Availability group as described in Clone SQL Server databases. Be sure to deselect Recover database after restore and to specify the same roll-forward time for each clone operation.

Add virtual databases to a new SQL Server AlwaysOn Availability group

After all databases for the new AG have been recovered, use SQL Studio or T-SQL to add the databases to the SQL AlwaysOn Availability group:

  1. From SQL Studio, select the SQL availability group.

  2. Add the databases to the availability group.

  3. Click Next. You are prompted to select a synchronization method. Select Join only; the Join only operation forms the primary and secondary databases into an availability group.

  4. Monitor the progress of the operation. When the operation is complete, both the primary and secondary databases are in sync.

  5. If autodiscover applications is not enabled, manually discover applications on the availability group host.

  6. After discovery is complete, the databases appear in the App Manager as members of the SQL availability group.

  7. Capture members of an availability group just like any other database.

The Backup and DR SQL Server DBA guide

This page is one in a series of pages specific to protecting and recovering Microsoft SQL Server databases with Backup and DR. You can find additional information in the following pages: