Add a database to AlwaysOn availability group automatically

Description: The following script adds new databases to AlwaysOn Availability Group. You can create it as a stored procedure.
To add a database to availability group automatically, you will need to create a server level trigger or a new database creation alert. Then, you call the script as a stored procedure from the server trigger or the database creation alert. I do not include server trigger on this page. You can find it on google. Thank you for reading!

NOTE : Before you run the stored procedure, please create a linked server between primary and secondary replica on both replica. Set RPC and RPC Out to True on the linked server properties

=============================================

DECLARE @linkedserver as SYSNAME
DECLARE @sql1 NVARCHAR(MAX)
DECLARE @sql2 NVARCHAR(MAX)
DECLARE @path VARCHAR(150)
DECLARE @bakupfile VARCHAR(200)
DECLARE @db VARCHAR(255)
DECLARE @BackUpsql NVARCHAR(MAX)

--Get a linked server information
SET @linkedserver = (select Name from sys.servers where server_id <> 0 and is_linked = 1)

-- Get back up path from database setting
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',  
@key = 'Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name = 'BackupDirectory', @path = @path OUTPUT ;  

--Added back slash at the end of back up path
Set @path = @path + '\'

DECLARE AG_cr CURSOR LOCAL FOR

 -- Get new databases which are not part of an availability replica of in availablity group

  SELECT name FROM sys.databases 
  WHERE replica_id IS NULL 
  AND database_id > 4 and 
  source_database_id is NULL 
  and State =0 and 
  name not in('distribution','ReportServer','ReportServerTempDB')

  OPEN AG_cr

  FETCH AG_cr INTO
  @db

WHILE @@FETCH_STATUS >= 0
        BEGIN

print @db
--Change database recovery mode to FULL

  IF (SELECT recovery_model FROM sys.databases WHERE name=@db) <> 1
        BEGIN
            PRINT 'Changing recovery model to FULL';
            DECLARE @ModeChange NVARCHAR(512) = 'ALTER DATABASE [' + @db + ']  SET RECOVERY FULL WITH NO_WAIT';
            EXEC sp_executesql @ModeChange;
        END;
    ELSE
        BEGIN
            PRINT 'Database is already in FULL recovery mode.'
        END;

Print 'Initial Database BackUp...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP DATABASE ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '.BAK'';'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;


Print 'Backing up a database...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP DATABASE ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '_AG.BAK'' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5;'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;

Print 'Backing up a Log...'
  SET @BackUpsql = N'';

  SELECT @BackUpsql += N'BACKUP LOG ' + QUOTENAME(@db)+ ' TO DISK = ''' + @path + @db + '_AGLOG.TRN'' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5;'
      
    PRINT @BackUpsql;
    EXEC master..sp_executesql @BackUpsql;

set @bakupfile = @db +'_AG.BAK'

set @sql1 = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N'' RESTORE DATABASE ' +@db+ ' FROM DISK ='+''''''+ @path + @bakupfile+''''''+ 'with norecovery'''

print @sql1

Exec sp_executeSQL @sql1

set @bakupfile = @db +'_AGLOG.TRN'

set @sql1 = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N'' RESTORE LOG ' +@db+ ' FROM DISK ='+''''''+ @path + @bakupfile+''''''+ 'with norecovery'''

print @sql1

Exec sp_executeSQL @sql1


--join AG group
DECLARE @AGroupName VARCHAR(200)
SET @AGroupName= (select Name from sys.availability_groups)


PRINT 'Joining database to availability group...'

DECLARE @AddDbToAG NVARCHAR(2048) = 'USE master' + char(13)+'ALTER AVAILABILITY GROUP [' + @AGroupName + '] ADD DATABASE [' + @db + ']';

EXEC sp_executesql @AddDbToAG;

PRINT 'Bringing replica online'     

DECLARE @AddHADR NVARCHAR(2048) = 'EXEC ' +'['+ @linkedserver+']'+'.master.dbo.sp_executesql N''ALTER DATABASE [' + @db + '] SET HADR AVAILABILITY GROUP = [' + @AGroupName +'];''';

EXEC sp_executesql @AddHADR


FETCH AG_cr INTO
  @db

END

 CLOSE AG_cr;
 DEALLOCATE AG_cr;