Script to Setup SQL Server Database Mirroring for Multiple Databases

Last year I had project that involved using database mirroring between two servers that had 20+ user databases on them and setting those up one a time takes way too long.  I wrote this script to generate script that could be ran in SQLCMD mode and setup mirroring between the two servers.  The @newmirror variable can be use to determine whether endpoints need to be  created or not.  I hope others will find it useful.

SET NOCOUNT ON

DECLARE	@primary VARCHAR(128) = 'primaryservername.domain.com' ,
	@secondary VARCHAR(128) = 'secondaryservername.domain.com' ,
	@account VARCHAR(128) = 'domain\username' ,
	@backupnetworkpath varchar(128) = '\\Servername\sharename\',
	@command VARCHAR(MAX) ,
	@command2 VARCHAR(MAX) ,
	@rowcount TINYINT = 0 ,
	@mirroringmode varchar(4) = 'Off'  --OFF or FULL
	@newmirror BIT = 1 --if mirroring has not been setup before create the endpoints

IF @mirroringmode = 'OFF' OR @mirroringmode = 'FULL'
BEGIN
	CREATE TABLE #databases ( name VARCHAR(128) )

	INSERT	INTO #databases
			SELECT	name
			FROM	sys.databases d
					LEFT OUTER JOIN sys.database_mirroring m ON m.database_id = d.database_id
			WHERE	name NOT IN ( 'dba', 'tempdb', 'master', 'msdb', 'model' )
					AND mirroring_role_desc IS NULL

	SET @rowcount = @@rowcount

	IF @rowcount > 0 
		BEGIN
			PRINT ':CONNECT ' + @primary
			SELECT	'ALTER DATABASE [' + name
					+ '] SET RECOVERY FULL WITH NO_WAIT'
			FROM	#databases

			IF @newmirror = 1 
				BEGIN 
					PRINT ''
					PRINT 'IF  NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N''Mirroring'')' 
					PRINT 'CREATE ENDPOINT [Mirroring] AUTHORIZATION [' + @account
						+ '] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)'
					PRINT ' GO'

					PRINT ''
					PRINT ':CONNECT ' + @secondary
					PRINT 'IF  NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N''Mirroring'')' 
					PRINT 'CREATE ENDPOINT [Mirroring] AUTHORIZATION [' + @account
						+ '] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)'
					PRINT 'GO'
				END

			PRINT ''
			PRINT ':CONNECT ' + @primary
			SELECT	'BACKUP DATABASE [' + name + '] TO  DISK = N''' + @backupnetworkpath + name
					+ '.bak'' WITH NOFORMAT, INIT,  NAME = N''' + name
					+ '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
			FROM	#databases

			PRINT ''
			SELECT	'BACKUP LOG [' + name + '] TO  DISK = N''' + @backupnetworkpath + name
					+ '.trn'' WITH NOFORMAT, INIT,  NAME = N''' + name
					+ '-Transaction Log  Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
			FROM	#databases

			PRINT ''
			PRINT ':CONNECT ' + @secondary
			SELECT	'RESTORE DATABASE [' + name + '] FROM  DISK = N''' + @backupnetworkpath + name
					+ '.bak'' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE, STATS = 10'
			FROM	#databases

			SELECT	'RESTORE LOG [' + name + '] FROM  DISK = N''' + @backupnetworkpath + name
					+ '.trn'' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
			FROM	#databases

			PRINT ''
			PRINT ':CONNECT ' + @secondary
			SELECT	'ALTER DATABASE [' + name + '] SET PARTNER = N''TCP://' + @primary + ':5022'''
			FROM	#databases

			PRINT ''
			PRINT ':CONNECT ' + @primary
			SELECT	'ALTER DATABASE [' + name + '] SET PARTNER = N''TCP://' + @secondary + ':5022'''
			FROM	#databases

			PRINT ''
			SELECT	'ALTER DATABASE [' + name + '] SET SAFETY ' + @mirroringmode
			FROM	#databases

			PRINT ''
			SELECT	'ALTER DATABASE [' + name + '] SET PARTNER TIMEOUT 30'
			FROM	#databases
			PRINT ''

			IF @newmirror = 1 
				PRINT 'exec sys.sp_dbmmonitoraddmonitoring'

			IF @newmirror = 1 
				BEGIN 
					PRINT ''
					PRINT ':CONNECT ' + @secondary
					PRINT 'exec sys.sp_dbmmonitoraddmonitoring'
				END
		END

	DROP TABLE #databases 
END