Restore a Database in the MS SQL Server Always On Availability Group

Share on:

Prerequisites

1. Backup File: Ensure you have a valid full backup of the database you want to restore. I used AdventureWorks2019.bak for my test.

2. Availability Group Configuration: Ensure the SQL Server instances are configured in an Always On Availability Group.

sql

3. Permissions: Ensure you have the necessary permissions to perform the restore operations.

 

Steps to Restore the Database

1. Restore the Database on the Primary Replica: First connect to the Primary Replica using SSMS, then right-click on the databases and click Restore Database

sql2

Then select the backup file for doing the restore operation.

sql3

If you want to relocate the restored file to another path use the File tab in the restore database.

sql4

In the options select “RESTORE WITH RECOVERY”

sql5

Click “OK”

sql6

 

2. Take a full backup from this restored database in the primary replica: Right-click on the database AdventureWorks2019 => Task => Back Up

sql7

Select the Full backup type, specify the path for the backup file, and click “OK.”

sql8

 

3. Take Transaction Log backup from the Primary Database: Right-click on the database AdventureWorks2019 => Task => Back Up

sql9

Select the Transaction log backup type, specify the path for the backup file, and click “OK.”

sql10

Now, we have the following files and should copy them to the Secondary Replica.

sql11

 

4. Restore all the backups (Full and  Transaction Log) in the Secondary Replica: Right-click on the databases and choose Restore Database

sql12

Select both files (Full and Transaction Log) for doing the restore operation

sql13

sql14

NOTE: In the secondary replica you must “RESTORE WITH NORECOVERY” to put your database in a recovery state for adding it to the availability group.

sql15

Click “OK”

sql16

Now if we connect to the secondary replica we can see that the AdventureWorks2019 database is restored.

sql17

 

5. Adding the Database to the Availability Group: Once the restore is complete on the primary and secondary replicas, we can add the database to the Availability Group.

In the primary replica right-click on the Availability Database => Add Database.

sql18

Now, you can see that we cannot add the AdventureWorks2019 to the Availability databases because the database is not in the Full Recovery Model.

sql19

Therefore, we must put the database (on primary replica) in full recovery mode.

Right-click on the AdventureWorks2019 database and select the properties.

sql20

Go to options and change Recovery Model to Full.

sql21

Now we can add this DB to the Availability Group

sql22

Connect to the Second Node

sql23

Because we already restored the database on other replicas, I selected the join-only option.

sql24

Click “Next”

sql25

Click “Finish”

sql26

Click “Close”

sql27

 

6. Verify the Database Synchronization: Check the synchronization state of the database on all replicas to ensure everything is working correctly.

You can see that this database exists in both primary and secondary replica availability databases.

sql28

TO Verify the Database Synchronization you can right-click on the availability group and select Show Dashboard.

sql29

OR you can use the following query.

SELECT ag.name AS AvailabilityGroupName,
       ar.replica_server_name AS ReplicaServerName,
       drs.database_id,
       db.name AS DatabaseName,
       drs.synchronization_state_desc AS SynchronizationState
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
JOIN sys.databases db ON drs.database_id = db.database_id
WHERE db.name = 'AdventureWorks2019';

sql30

Share on:

More from this Author

Set up DELTA (DB Endpoint Latency Testing Ammeter) to Test the Latency from a Windows System using venv to an Oracle Database

Set up DELTA (DB Endpoint Latency Testing Ammeter) to Test the Latency from a Windows System using venv to an Oracle Database

What is the DELTA? DELTA (DB Endpoint Latency Testing Ammeter) is a tool or framework designed to measure the latency of database endpoints. It is ... Read More

Encrypt an Existing MS SQL Server 2019 Database in Always Environment Using TDE

Encrypt an Existing MS SQL Server 2019 Database in always on Environment using TDE

Transparent Data Encryption (TDE)  Transparent Data Encryption (TDE) in SQL Server protects data at rest by encrypting database data and log files ... Read More

Back to Top