How to programmatically add a database to an existing Always On Availability Group (AOAG)?

Question

I'd like to programmatically (T-SQL scripts) create a new database, and then add it to an existing Always On Availability Group (AOAG).

So far, I'm ok with creating the database, creating a full back-up (which is a pre-requisite for adding a database to AOAG) and to add it to the existing AOAG.

However, I don't know how to initialize the initial replication to the secondary database servers.

Here's my script so far:

CREATE DATABASE Test;
GO

USE Test;
GO

BACKUP DATABASE Test
TO DISK = 'D:\Sync\Test.Bak'
   WITH FORMAT,
      MEDIANAME = 'D_Sync',
      NAME = 'Initial Full Backup for restore in AOAG group';
GO

USE Master
GO

ALTER AVAILABILITY GROUP AOAG_APP ADD DATABASE Test; 
GO

If I was using MS SQL Server Management Studio, I would use the wizard and in the Select Data Synchronization options, I would select Full, and then the path to the back up files.

How can I achieve the same with a SQL script? It doesn't seem the ALTER AVAILABILITY GROUP <group> ADD DATABASE <database> has the options to specify Full and the path to backup files.

Any help would be much appreciated!

Answer 1

You can try with the code below.

--On primary node

create database test
alter database test set recovery FULL
backup database test to disk='Z:\Backups\Test Folder\test.bak'
backup log test to disk='Z:\Backups\Test Folder\test.trn'
alter availability group availablitygroup_name
add database [test];

--On secondary node

Restore database test from disk='\\node1\Backups\Test Folder\test.bak' with norecovery
restore log test from disk='\\node1\Backups\Test Folder\test.trn' with norecovery
alter database test set HADR availability group= availablitygroup_name

--On primary node

use test
go
create table abc(name varchar(15))
Insert into abc values('amarnath')
select * from abc

--On secondary node

use test
go
select * from abc