CREATE PROCEDURE dbo.AddNewDBsToGroup
@group SYSNAME = N'sql01_sme_AG', -- *** SPECIFY YOUR GROUP NAME HERE ***
@debug BIT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@sql NVARCHAR(MAX) = N'';
DECLARE @t TABLE(db SYSNAME);
INSERT @t SELECT name FROM sys.databases
WHERE replica_id IS NULL AND database_id > 4;
-- add the database to the group on the primary:
SELECT @sql += N'ALTER AVAILABILITY GROUP '
+ QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
FROM @t;
IF @debug = 1
BEGIN
PRINT @sql;
END
ELSE
BEGIN
EXEC master..sp_executesql @sql;
END
END
GO
EXEC dbo.AddNewDBsToGroup @debug = 0;