Restore database in SQL Server Always On Availability Group

In this blog, we will look at how to restore a database participating in SQL Server Always On Availability Group using the T-SQL scripts.

Suppose you have configured the availability group for your database [ABCD] below.

Production environment:

• Primary Replica: ServerP1

• Secondary Replica: ServerP2

• AG database: [ABCD]

Staging environment

· Primary Replica: StageP1

· Secondary Replica: StageP2

· AG database [ABCD]

DBAs get a common request to refresh the non-prod databases with the production backup Therefore, in this case, I need to restore database [ABCD] from production environment to Staging environment.

How do you restore a database participating in the availability group?

Let’s figure it out in this article.

Step 1: Take database backup from Primary replica with COPY_ONLY

First step is to take a full database backup from the primary AG replica of production instance. You can use the following script for backup.

BACKUP DATABASE <DBName> to DISK = '<Backup Directory>'

WITH COPY_ONLY, COMPRESSION, STATS=10

Step2 : Copy the backup from Production to Staging environment

Once the backup completes, you need to copy backup file from production to non-prod environment.

Note: The following steps needs to perform on staging environment where we need to restore the database.

Step 3: Remove the database from the availability group

Connect to the primary replica instance and run the following script after modifying values as per your DB environment.

USE [master]
GO
ALTER AVAILABILITY GROUP <AvailabiltyGroupName>
REMOVE DATABASE <DBName>;
GO

Step 4: Drop database on Secondary replica

In this step, we need to drop the database on the secondary replica.

USE [master]
GO
DROP DATABASE <DBName>;
GO

Step 5: On primary Replica Restore the database from the backup

Once the database is out of the availability group, we need to restore it on the primary replica. You can use the REPLACE keyword to replace the existing database.

Restore database <DBName> From DISK='<Backup File location>’
WITH REPLACE, STATS=10

Step 6:Add database back into availability group on primary replica

This article assumes that you have availability group with automatic seeding feature. Therefore, we do not restore database manually on secondary replica.

However, you can restore the database on secondary replica in NORECOVERY MODE and join the database into availability group.

The following statement adds the database into availability group and use the automatic seeding for synchronization.

ALTER AVAILABILITY GROUP <AvailabiltyGroupName> ADD DATABASE <DBName>;
GO

Step7: On primary Replica Check availability group synchronization

You can monitor the synchronization similar to AG dashboard using the following query. Execute this query on the primary replica.

DECLARE @HADRSERVERNAME VARCHAR(25) 
SET @HADRSERVERNAME = @@SERVERNAME 
SELECT CLUSTERNODES.GROUP_NAME          AS [AVAILABILITY GROUP NAME], 
       CLUSTERNODES.REPLICA_SERVER_NAME AS [AVAILABILITY REPLICA NAME], 
       CLUSTERNODES.NODE_NAME           AS [AVAILABILITY NODE], 
       RS.ROLE_DESC                     AS [ROLE], 
       DB_NAME(DRS.DATABASE_ID)         AS [AVAILABILITY DATABASE], 
       DRS.SYNCHRONIZATION_STATE_DESC   AS [SYNCHRONIZATION STATUS], 
       DRS.SYNCHRONIZATION_HEALTH_DESC  AS [SYNCHRONIZATION HEALTH] 
FROM   SYS.DM_HADR_AVAILABILITY_REPLICA_CLUSTER_NODES CLUSTERNODES 
       JOIN SYS.DM_HADR_AVAILABILITY_REPLICA_CLUSTER_STATES CLUSTERSTATS 
         ON CLUSTERNODES.REPLICA_SERVER_NAME = CLUSTERSTATS.REPLICA_SERVER_NAME 
       JOIN SYS.DM_HADR_AVAILABILITY_REPLICA_STATES RS 
         ON RS.REPLICA_ID = CLUSTERSTATS.REPLICA_ID 
       JOIN SYS.DM_HADR_DATABASE_REPLICA_STATES DRS 
         ON RS.REPLICA_ID = DRS.REPLICA_ID 

You can refer to article - Restore an existing availability group database participating in SQL Server Always On Availability Groups on SQLShack for more details.