Updated: Dec 31, 2021
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.
Primary Replica: ServerP1
Secondary Replica: ServerP2
AG database: [ABCD]
· 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.