Restoration of database which is part of Always-on availability Group

We got a request from a user to restore a database from Production to DEV environment and user shared the name of Prod instance & Prod database name which need to be restored on AG Listener which is client access point in Always-on.

Production Server database: PROD\Pv_capital

AG Listener Name: AGLSNR

1. We connect to the Listener and find out the Primary Replica name using below SQL command:

Select serverproperty(‘ComputerNamePhysicalNetBios’)

Once we got the name of Primary AG Replica, we will find-out respective secondary AG Replica. Here is the detail of Both Primary and Secondary AG Replica:

Primary AG Replica: DEV105B

Secondary AG Replica: DEV105A

2. Backup the database on PRDO instance and copy the backup file to both Primary (DEV105B) and secondary AG Replica (DEV105A) servers.

3. Connect to primary replica and remove the database from AG group before restoration using

Below command:

Alter availability group [AG01_DEV105] remove database [Pv_capital]

When we execute this command our primary database will not be part of AG group and its corresponding secondary database will become in restoring (Non-functional) state as user cannot connect to the database.

4. Now restore the database on primary server from backup with Recovery option.

5. Same, restore the secondary database on secondary server (DEV105A) from backup file we copied earlier but with NORECOVERY option.

6. Now expand [AG01_DEV105] AG group, right click on “Availability databases” option then click on “Add database” which will open below wizard.

Click next to continue with Wizard.

Select the database which needs to be added.

Click next to continue with wizard

Here I selected “Join only” option because I have already restored secondary database. Else we can choose First option “Full” in which we have to provide a shared path which must be accessible by both primary and secondary servers. In the shared path full and log backup of primary database is created and restored on the secondary server automatically.

Note: If both primary and secondary servers are not having same disk layout then this wizard will fail.

Click next to continue with wizard