Restore a Database Participating in Alwayson Availability Group

Sometimes, we need to restore a database that is part of always on availability group to fulfill our business needs. There might be several reasons behind doing this. Restore a database in availability group is different than restoring a normal database. You cannot restore Availability Group database directly as you do for other databases. You will get below error message while doing this.

The operation cannot be performed on database “DBNAME” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

As per above error details, we cannot restore a database that is part of always on availability group directly. Here, I will explain step by step process to restore such databases.

Restore Databases in Availability Group

If you need to restore an availability group database, you need to first evict that database from availability group and then restore that database and finally you can re-add it to the always on availability group. So, there are three steps involved in restoring an AOAG database.

1. Remove identified database from Availability Group.
2. Restore that database.
3. Add database to Availability Group post restore operation.

Let’s explain each steps in detail in order to restore an alwayson database.

Remove Database from Availability Group

First step is to remove the identified database from AOAG configuration to restore AOAG database. If you have to restore primary database in AOAG configuration then you should remove it from the primary replica that will automatically remove this database from AOAG configuration on secondary replica as well. You don’t need to separately remove secondary database from AOAG configuration. We can use below T-SQL statement to remove your primary database from AOAG configuration.

--Change name of AG_Techyaz to your Availability group.
--Change database name from Techyaz to your primary database.
ALTER AVAILABILITY GROUP AG_Techyaz REMOVE DATABASE Techyaz

You can use GUI method as well to remove primary database from AOAG configuration. Read attached article to understand step by step process to remove a database from Availability group using GUI method.

Restore Database

Once primary database will be removed from availability group, It will disappear from Availability Database folder showing under your Availability group name on both replicas. Now, next step will be database restore. Go ahead to restore your database.
Here, we have to restore our database till 25 Jan 8 AM to recover point in time data. You can use your backup files as per your requirement. You can use either GUI method  in SSMS or T-SQL method to restore your database. Run below command to restore this database.

--Restore primary database with the help of your backup file.
RESTORE DATABASE Techyaz
FROM Disk= ‘F:\Backups\Techyaz_Full.bak’
WITH NORECOVERY, REPLACE;

--Restore log backup till the time you want to recover the database.
RESTORE LOG Techyaz
FROM Disk= ‘F:\Backups\Techyaz_TLog.trn’
WITH NORECOVERY, REPLACE, STOPAT = 'Jan 25, 2018 8:00 AM';

--Bring database Online.
RESTORE DATABASE Techyaz WITH RECOVERY;

Once your database will be restored and comes online, we will go ahead to add this database to availability group again.

Add database to Availability Group

If you want to re-add this database to availability group then you must perform this step otherwise your database will not have any HA or DR capability. I would recommend to first prepare your corresponding secondary database before starting to add it to availability group. If you have a plan to prepare secondary database during adding it to availability group then the processing will take lot of time for huge databases.

To prepare the secondary database, you first need to take a full backup and immediate transaction log backup of your newly restored primary database. Copy these files to secondary replica and restore corresponding secondary database in norecovery mode. Below are high level steps you need to perform to add this database to AOAG configuration.

1. Run full backup and immediate transaction log backup of primary database on primary replica.
2. Copy both full backup and log backup files to secondary replica.
3. Restore corresponding secondary database with the help of above copied files in norecovery mode with REPLACE option.
4. Now, run below T-SQL command to add this database to existing availability group. If you want to add this database to existing AOAG configuration using GUI method in SSMS, visit attached link where I have described it in step by step process.

-- Connect to the server instance that hosts the primary replica.
-- Add an existing database to the availability group.
ALTER AVAILABILITY GROUP AG_Techyaz ADD DATABASE Techyaz;
GO

Make sure to configure your secondary database on secondary replica as we have done from step 1 to step 3.
If you don’t want to add this database to availability group then you can ignore this step and use your database for transactions. You can also remove its corresponding database that is in restoring state on secondary replica. If you want to use that database from secondary replica for any purpose you can bring it online by running below command on secondary replica.

--Run on secondary replica in case you don't want to configure this database in AOAG rather want to use it for transactions.
RESTORE DATABASE Techyaz WITH RECOVERY;

Now, you are done with database restore in Alwayson Availability Group. Validate it by launching dashboard report or looking in to SSMS to get AOAG health for newly added or restored database.
I hope you like this article. Please comments us and follow our facebook page and Twitter handle to get latest updates.

Read More:

• Understanding Backup Preferences in Alwayson Availability Group
• AOAG Listener Error 19471: The handle is Invalid
• Secondary Replica Showing in Resolving State after AOAG Automatic Failover
• Fix Error 976: Cannot Connect to Secondary Replica

• Author
• Recent Posts

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.

Follow me:

Latest posts by Manvendra Deo Singh (see all)

• How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
• How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
• How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023