Background
There are two clustered servers running SQL Server 2014. The servers host production databases as well as databases used for QA and testing. One AlwaysOn Availability Group has been created for the production databases and one for the QA databases. The same server serves as the primary for both AlwaysOn Availability Groups.
One of the production databases needs to be restored from backup. Backups are taken from the secondary server, not the primary. The backups should be restored to the same server from which they were taken.
The following tasks need to be completed in order to restore the database:
- Make the secondary server from which the backups were taken the primary server
- Remove the database to be restored from the AlwaysOn Availability Group
- Restore the database
- Add the database back into the Always Availability Group
Following are detailed instructions for completing these tasks.
Task 1: Switch the Primary and Secondary Servers
1) Connect to both servers in the cluster in SQL Server Management Studio.
2) On the Secondary server, expand the "Availability Groups" folder under the "AlwaysOn High Availability" folder.
3) Right-click on the availability group containing the database to be restored and select "Failover…" from the context menu. Click “Next >”.
4) Select the new primary server. Click “Next >”.
5) Verify the choices and click “Finish”.
6) Repeat steps 3-5 for the remaining availability group.
Task 2: Remove the Database from the Availability Group
A restore operation cannot be performed on a database that is part of an availability group, so the next task is to remove the database from the group.
1) On the new primary server, expand the list of Availability Databases for the availability group.
2) Right-click the database to be restored and select "Remove Database from Availability Group…" from the context-menu.
3) Click “OK” to remove the database from the availability group.
Task 3: Restore the Database
1) In the “Databases” folder on the primary server, right-click on the database to be restored and select "Properties" to open the “Database Properties” dialog. Select the “Options” page, scroll down to the “Restrict Access” option, and change the value from MULTI_USER to SINGLE_USER. Click “OK”.
2) In the “Databases” folder on the primary server, right-click on the database to be restored and select Tasks->Restore->Database… from the context menu.
3) On the “General” page of the “Restore Database” dialog, select the last Full backup and all Transaction log backups.
4) Select the “Options” page of the “Restore Database” dialog and click the "Overwrite the existing database (WITH REPLACE)" option. Click "OK".
Task 4: Add the Database Back to the Availability Group
After the restore of the database to the new primary server is complete, it can be put back into the availability group.
1) In the “Database” folder on the secondary server, right-click the database and select "Delete" from the context menu. Click “OK”.
2) Right-click “Availability Databases” in the availability group on the primary server and select "Add Database…" from the context menu. Click “Next >”.
3) Select the database to be added to the group and click "Next >".
4) Select "Full" as the data synchronization preference. This will take a full backup of the database on the primary and restore it on the secondary server(s). Specify a network location accessible to the primary and all secondary servers in which to place the backup files. Click "Next >".
5) Use the “Connect…” button to establish a connection to the secondary server(s). Click "Next >".
6) The "Add Database to Availability Group" wizard will validate all of the settings for the new availability group database. When it completes, click "Next >".
7) Verify the choices and click "Finish" to add the database to the availability group.
Final Tasks
The restore of a database in an AlwaysOn Availability Group is now complete.
At this point it is recommended to immediately perform a backup of the restored database on the secondary server. This will establish a new backup chain for the database.
The backup files created during synchronization of the primary and secondary server(s) can be deleted. The location of those files was specified in Step 4 of the “Add the Database Back to the Availability Group” task.
Note that the restored database should now be back in MULTI_USER mode. Recall that it had been set to SINGLE_USER in Step 1 of the “Restore the Database” task.
No comments:
Post a Comment