TSQL to add a database to existing Availability group

Following are the TSQL statements that can be used to add database into existing Availability group.


--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect PrimaryServer

USE [master]

GO

ALTER AVAILABILITY GROUP [AvailGroup]
ADD DATABASE [MyDatabase];

GO

:Connect PrimaryServer

BACKUP DATABASE [MyDatabase] TO  DISK = N'\\Sharedlocation\FULL\MyDatabase.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect SecondaryServer

RESTORE DATABASE [MyDatabase] FROM  DISK = N'\\Sharedlocation\FULL\MyDatabase.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect PrimaryServer

BACKUP LOG [MyDatabase] TO  DISK = N'\\Sharedlocation\FULL\MyDatabase_20160624104225.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:Connect SecondaryServer

RESTORE LOG [MyDatabase] FROM  DISK = N'\\Sharedlocation\FULL\MyDatabase_20160624104225.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:Connect SecondaryServer


-- 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'AvailGroup'
	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 [MyDatabase] SET HADR AVAILABILITY GROUP = [AvailGroup];

GO

--
--
Advertisements
This entry was posted in High Availability, Productivity, SQL, SQL Server, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s