Restoring a SQL Server availability group database to the primary and the secondary replicas
In some situations you may need to restore the SQL Server availability group (AG) databases to both the primary and the secondary replica(s). These situations can include when you restore databases:
To restore a SQL Server availability group database to the primary and the secondary replicas
- If you did not already, configure the mappings for distributed application restores.
Map the WSFC (Windows Server Failover Cluster) name to each AG node. If you have an AG with an FCI, you must configure additional mappings. Configure these mappings in the Distributed Application Restore Mapping host property on the master server.
See Configuring mappings for restores of a distributed application, cluster, or virtual machine .
- If you did not already, review the auto-discovered mappings for the hosts in your environment.
Approve each valid Auto-Discovered Mapping that NetBackup discovers in your environment. Perform this configuration in the Host Management properties on the master server.
See Reviewing the auto-discovered mappings in Host Management.
- Log on to the AG node that hosts the primary replica.
- Open SQL Server Management Studio and perform the following tasks:
Suspend data movement on the database.
Remove the database from the AG.
- Close any connections to the database.
- Remove the primary database from SQL Server.
- Open the NetBackup MS SQL Client.
- Select File > Set SQL Server connection properties.
- From the Instance list, select the instance that hosts the AG.
- Select File > Restore SQL Server objects.
You may want to perform this restore for the primary database in parallel with the restore(s) for the secondary database(s).
- In the Backup History Options dialog box, for the Source Client, select or type the full qualified domain name (FQDN) of the Windows Server Failover Clustering (WSFC) cluster.
You can find the cluster name in Failover Cluster Manager or the job details for the backup.
NetBackup displays the databases that are included in the availability group. To restore any system or any user databases in the backup, perform a separate browse and restore operation using the node name.
- Click OK.
- In the Restore Microsoft SQL Server Objects dialog box, select the latest full backup image and transaction log backups.
- Select Use replace option.
- From the Recovery list, select Recovered.
- Click Restore.
- When the restore completes, add the database to the AG using the Skip initial data synchronization option.
- Log on to the node that hosts the secondary replica.
- Close any connections to the database on the secondary replica.
- Remove the secondary database from SQL Server.
- Open the NetBackup MS SQL Client.
- Select File > Set SQL Server connection properties.
- From the Instance list, select the instance that hosts the AG.
- Select File > Restore SQL Server objects.
- In the Backup History Options dialog box, for the Source Client, select or type the full qualified domain name (FQDN) of the Windows Server Failover Clustering (WSFC) cluster.
You can find the cluster name in Failover Cluster Manager or the job details for the backup.
NetBackup displays the databases that are included in the availability group. To restore any system databases or user databases in the backup, perform a separate browse and restore operation using the node name.
- Click OK.
- In the Restore Microsoft SQL Server Objects dialog box, select the same set of images that you restored to the primary replica.
- From the Recovery list, select Not recovered.
- Select Use replace option.
- If the nodes in the AG use different paths for the database file, you need to create a move template to restore to a secondary replica. From the Scripting list, choose Create a move template.
- Click Restore.
- When the restore completes, join the database to the AG.
- Repeat step 18 through step 32 for additional nodes in the AG.