Here's the answer and AlwaysOn Script. It will (if scheduled) automatically add databases to AlwaysOn as per the settings in your SQL Server job schedule.

Some caveats to the script:

?the \backups location is not set in a variable. It is hard-coded in the script. That share name/path must be modified for the individual set of servers this is applied to.

?This is made for a 2-server configuration- a Primary and a Secondary.

?Be sure to keep the varchar setting of (400) as such; if it is 200 or so, the script fails as the setting is not big enough.

?If you run this script manually in SSMS, be sure to run the command “DROP TABLE dbo.#dbs” first, otherwise it won’t run manually in SSMS. It will run perfectly as a SQL Server Agent job, however. (not sure why that is!)

?The first 2 lines are also “hard-coded” in, (xp_cmdshell 'del /F /Q /S \\atel-web\be1\backups\*.*') so that the old SQL-server “Initialization” backup jobs get cleared out.

xp_cmdshell 'del /F /Q /S \\atel-web\be1\backups\*.*'

exec xp_cmdshell 'del /F /Q /S \\atel-web\be2\backups\*.*'

USE master

DECLARE @secondaryservername nvarchar(50)

DECLARE @primaryservername nvarchar(50)

DECLARE @availabilitygroup nvarchar(50)

SET @secondaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states

, sys.dm_hadr_availability_replica_cluster_states

where role_desc = 'SECONDARY' AND sys.dm_hadr_availability_replica_states.replica_id =

sys.dm_hadr_availability_replica_cluster_states.replica_id)

SET @primaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states

, sys.dm_hadr_availability_replica_cluster_states

where role_desc = 'PRIMARY' AND sys.dm_hadr_availability_replica_states.replica_id =

sys.dm_hadr_availability_replica_cluster_states.replica_id)

SET @availabilitygroup = (SELECT name FROM [sys].[availability_groups])

IF OBJECT_ID('dbo.#dbs', 'U') IS NOT NULL

DROP TABLE dbo.#dbs

IF (SELECT CURSOR_STATUS('global','adddbs')) >=0

BEGIN

DEALLOCATE adddbs

END

create table #dbs(a int primary key identity, dbname varchar(100))

declare @nextdb varchar(100)

declare @restorestring varchar(400)

--Populate temp table

insert into #dbs(dbname)

select name from sys.databases

where group_database_id is null and replica_id is null

and name not in('master','model','msdb','tempdb')

--Create a cursor to

declare adddbs cursor for

select dbname from #dbs

open adddbs

FETCH NEXT FROM adddbs

INTO @nextdb

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC ('ALTER DATABASE' + '[' + @nextdb + ']' + 'set RECOVERY FULL')

EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + 'initialize.bak''')

EXEC ('ALTER AVAILABILITY GROUP ['+ @availabilitygroup +'] ADD DATABASE ' + '[' + @nextdb + ']')

EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + '.bak''')

EXEC ('BACKUP LOG ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + '_log.bak''')

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE DATABASE ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\\' +@primaryservername

+'\backups\' + '[' + @nextdb + ']' + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"'

exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE LOG ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\\' +@primaryservername+'\backups\' +

'[' + @nextdb + ']' + '_log.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"'

exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"ALTER DATABASE ' + '[' + @nextdb + ']' + ' SET HADR AVAILABILITY GROUP = [' + @availabilitygroup

+']"'

exec xp_cmdshell @restorestring

FETCH NEXT FROM adddbs

INTO @nextdb

END

CLOSE adddbs

DEALLOCATE adddbs