Script to add database in existing availability group by Tsql.

This is the script to include database in existing Always on availability group by passing below parameters.

1. Primary server name, port number

2.Secodnary server name , port number

3. Database name

4.Availability group name

5.Shared path on primary server with read and write access for dba id required to take backup from primary server for HADR configuration.

6. This script to be executed in SQLCMD only.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect Primary-Server_Name,5008

USE [master]

GO

ALTER AVAILABILITY GROUP [Availability group NAME]

ADD DATABASE [Database Name];

GO

:Connect Primary-Server_Name,Port_Number

BACKUP DATABASE [Database Name] TO  DISK = N'\\Primary-Server_Name\Always_on_Backup

\Database Name.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD,

COMPRESSION,  STATS = 5

GO

:Connect Secondary-Server_Name,Port_Number

RESTORE DATABASE [Database Name] FROM  DISK = N'\

\Primary-Server_Name\Always_on_Backup\Database Name.bak' WITH  NORECOVERY,  NOUNLOAD,

 STATS = 5

GO

:Connect Primary-Server_Name,Port_Number

BACKUP LOG [Database Name] TO  DISK = N'\\Primary-Server_Name\Always_on_Backup

\Database Name_20170609165021.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND,

NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect Secondary-Server_Name,Port_Number

RESTORE LOG [Database Name] FROM  DISK = N'\\Primary-Server_Name\Always_on_Backup

\Database Name_20170609165021.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect Secondary-Server_Name,Port_Number

-- Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier 

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes 

if (serverproperty('IsHadrEnabled') = 1)

                and (isnull((select member_state from master.sys.dm_hadr_cluster_members where

upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty

('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <>

0)

                and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

    select @group_id = ags.group_id from master.sys.availability_groups as ags where name

= N'Availability group NAME'

                select @replica_id = replicas.replica_id from master.sys.availability_replicas as

replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper

(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

                while @conn <> 1 and @count > 0

                begin

                                set @conn = isnull((select connected_state from

master.sys.dm_hadr_availability_replica_states as states where states.replica_id =

@replica_id), 1)

                                if @conn = 1

                                begin

                                                -- exit loop when the replica is connected, or if the query

cannot find the replica status

                                                break

                                end

                                waitfor delay '00:00:10'

                                set @count = @count - 1

                end

end

end try

begin catch

                -- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE [Database Name] SET HADR AVAILABILITY GROUP = [Availability group NAME];

GO

GO

 Hope adding database to an existing Always on availability group can be done in TSQL script.

 If more number of databases to be added in Always on availability group, We can do it by repeating above steps in Sql server management studio new query window .