Sometimes there is requirement to perform database restore on primary database that is participating in Always On Availability Group(AOAG) setup and we can not restore database directly and in such cases, we have to break the AOAG setup to perform database restore and then re-setup AOAG. We will follow step by step process to achieve the same using T-SQL method. Also note that this particular step is applicable for Basic Availability Group(BAG) setup.

Server/AG details for reference purpose:

• AOAG Name: Test_AG
• Primary Database: Test
• Secondary Database: Test

High level steps:

• Break the AOAG
• Perform database restore
• Re-Setup AOAG

Step#1: Break the AOAG:

Step#1.1: Run below query on Secondary database

DROP AVAILABILITY GROUP [Test_AG]
go
DROP DATABASE Test
go

Step#1.2: Run below query on Primary database:

ALTER AVAILABILITY GROUP [Test_AG] remove database Test

Step#2: Database Restore on Primary:

RESTORE DATABASE Test
FROM Disk= 'F:\Backups\Test_Full.bak'
WITH MOVE 'Test_data' to 'F:\Data\Test.mdf',
MOVE 'Test_Log' to "F:\Log\Test.ldf', REPLACE
go

Step#3: Re-Setup AOAG:

Step#3.1: Run below query on Primary database:

ALTER AVAILABILITY GROUP [Test_AG] GRANT CREATE ANY DATABASE;

Step#3.2: Run below query on Secondary database:

ALTER AVAILABILITY GROUP [Test_AG] JOIN WITH(CLUSTER_TYPE=NONE)
go
ALTER AVAILABILITY GROUP [Test_AG] GRANT CREATE ANY DATABASE
go

Step#3.3: Run below query on Primary database:

ALTER DATABASE [Test] SET RECOVERY FULL WITH NO_WAIT
go
BACKUP DATABASE [Test] to disk='F:/Backups/Test_AG_BACKUP.bak'
go
ALTER AVAILABILITY GROUP [Test_AG] ADD DATABASE [Test]
go