Rajendra Gupta
Healthy AG dashboard

Restore an existing availability group database participating in SQL Server Always On Availability Groups

September 8, 2020 by

In this 20th article for the SQL Server Always On Availability Group series, we will cover the steps to restore an existing availability group database.

Requirement

Suppose we have multiple database environments for a critical application. These environments can be development, stage, UAT (user acceptance testing). You might have configured the development database also in an availability group configuration. Usually, developers ask to refresh the development databases from production data. It helps them to test the code for production data before actual production implementation. Refreshing the lower environment databases might be a regular(monthly) activity as well, so you can automate the steps easily using the SQL scripts.

You might think a question here– We can take production database backup and restore it on the development database. What difference does it make in a standalone database restore or availability group database restore?

Database restore works with the standalone database, but if the database is configured in the availability group, we cannot directly restore the database. It requires additional steps because of the AG configurations. Our database should be in the same state (AG synchronized) after the database restores as well.

In this article, let’s cover the steps to restore an existing availability group database in the SQL Server Always On Availability Group.

Prerequisites

You should have two or more nodes in SQL Server Always On Availability Group configuration.

In this article, we have the following environment for demonstration purposes,

  • Nodes: SQLAG1 & SQLAG2
  • Availability Group: [SQLLogShipAG]
  • Database: [SQLShackDemo]
  • Synchronization state: Synchronized
  • Failover Mode: Automatic
  • AG dashboard: Healthy

SQL Server Always On Availability Group dashboard

You can follow the SQL Server Always On Availability Group series (ToC at the bottom) to prepare the complete AG environment for this article. You should also understand the availability group concepts to be familiar with its terminology.

Steps to restore an availability group database in SQL Server Always On Availability Groups

Suppose you require to restore the [SQLShackDemo] database from the production to the development environment. In my development environment, the database is part of the availability group.

I took the production database full backup and copied the backup file on the development server. To restore this backup on a development instance, right-click on the development [SQLShackDemo] database and navigate to Tasks -> Restore-> Database. In the restore database wizard, specify the backup file location and click ok. You will get the following error message.

restore an availability group database

As per the error message, we cannot restore the database because it is part of an availability group. To restore the availability group database, we need to evict the database from the availability group, restore the database, and add in the availability group. Let’s explore the details step and restore the database.

Step 1: Remove the database from an availability group in SQL Server Always On Availability Groups

Connect to the primary replica instance in SSMS and expand availability databases. You might have multiple availability groups in a SQL instance. You need to remove the specific database from the availability group.

Right-click on the database and click on Remove Database from Availability Group

Remove Database from Availability Group

It opens the following remove database from the availability group wizard.

availability group wizard

You can also generate the script from this wizard. It uses the Alter availability group statement with the Remove database clause.

It removes the database from the availability group, so if you refresh the AG dashboard, it shows you the following error and warnings. It shows synchronization status also as Not Synchronizing in the dashboard.

Dashboard error

Click on the critical error in the availability group state. It does not point to you that there is no database in the availability group. It says that some availability replicas are not synchronizing data.

Detailed error message

Step 2: Restore the database from the production backup in SQL Server Always On Availability Groups

Now, you can restore the database similar to a standalone database. The database is in the online status on the primary replica; therefore, in the restore wizard, it asks you for the tail-log backup.

Restore the database

Click on Options and do the following tasks.

  • Remove the tick from the Take tail-log backup before the restore. We do not want any tail-log backup and want to restore the production database backup. You can refer to Tail-Log Backup and Restore in SQL Server for more details on tail-log backups
  • Put a check on the Close existing connections to the destination database. It removes all existing database connections and makes the database available for the restore. If you do not put this check on the restore wizard and the database has existing connections, you get an error message about existing connections. In that case, you have to close all existing connections using the KILL SPID statement manually

take log backup

Click Ok to start database restore. You get another error because the database is online, and we have a disabled tail-log backup. We need to replace the database using WITH REPLACE clause in the restore database statement.

Restoration error

To replace the database, put a check on the Overwrite the existing database (WITH REPLACE).

Overwrite the existing database

It restores the [SQLShackDemo] successfully. The database restoration time depends upon the database size and the storage disk IOPS.

Successful DB restore

Step 3: Add database back in the SQL Server Always On Availability Group

In the previous step, we restored the database on the primary replica. This database needs to be added again to the availability group.

In the primary replica availability group, right-click on the availability databases and add the database.

Add database back in the SQL Server Always On Availability Group

In the Add Database to Availability Group wizard, select the database SQLShackDemo. This database meets prerequisites for the AG.

DB status

Connect to existing secondary replicas.

Connect to existing secondary replicas

In the next part, we need to select how data synchronization happens from the primary to the secondary replica. You can prepare your database before adding it to the availability group.

data synchronization

To prepare the secondary database, You can do the following tasks.

  • Take a full and transaction log backup on the primary replica
  • Restore these backup in the secondary replica in NORECOVERY mode

In this article, we use the automatic seeding that creates the secondary database automatically. It also synchronizes both primary and secondary replicas. You must have good network bandwidth for the automatic data seeding, especially for a vast database.

In my case, the database size is small, so that we can go ahead with the automatic seeding. In the next step, it performs validation.

We get an error message in the below screenshot. In the step1, we removed the database from the primary replica availability group. It evicts the database from the AG but does not remove the secondary replica database. The secondary replica database goes into the restoring mode.

We need to connect to the secondary replica instance and delete the database manually if you use automatic seeding. In the case of backup restore, we can use the WITH REPLACE clause to replace the existing database.

DB validation

To resolve this error, execute the following script on the secondary replica instance.

  • It deletes the backup history for the [SQLShackDemo] database from the MSDB
  • It drops the database

After dropping the database, click on re-run validation, and it shows successful validations.

Drop databases

Verify your choices in the add database wizard. We can note that it adds [SQLShackDemo] database in the [SQLLogShipAG] availability group using automatic seeding.

Verify choices

In the next step, verify the results. It adds the database in the availability group.

add the database in AG

Verify the availability group database in the object explorer and dashboard.

Healthy AG dashboard

Conclusion

In this article, we explored the overall process to restore an availability group database in the SQL Server Always On Availability Group.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views