Script to Setup SQL Server Database Mirroring for Multiple Databases

Last year I had a 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 a script that could be run in SQLCMD mode and setup mirroring between the two servers.  The @newmirror variable can be used to decide 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, COPY_ONLY, 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, COPY_ONLY 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

Related Posts

3 thoughts on “Script to Setup SQL Server Database Mirroring for Multiple Databases

  1. Thanks for the script Tracy. However how can i use this script if my secondary databases are already restored and in no recovery. Actually they were earlier part of LS. We have broken LS and now need to convert to mirror. So how can this be achieved as your script needs shared backup folder. Also what @account do i need to use in script above?

    1. I’m not sure you can, you have to have all your log backups to get caught up. My script is designed for when you are starting from scratch. And because you are going to two different servers are going to need a shared location to access the backups.

  2. Thanks dude

    changed to work on sql2005

    DECLARE @primary as VARCHAR(128)
    DECLARE @secondary as VARCHAR(128)
    DECLARE @account as VARCHAR(128)
    DECLARE @backupnetworkpath as varchar(128)
    DECLARE @command as VARCHAR(MAX)
    DECLARE @command2 as VARCHAR(MAX)
    DECLARE @rowcount as TINYINT
    DECLARE @mirroringmode as varchar(4) –OFF or FULL
    DECLARE @newmirror as BIT –if mirroring has not been setup before create the endpoints
    set @primary = ‘primary’
    set @secondary = ‘sec’
    set @account = ‘domain\acc’
    set @backupnetworkpath = ‘\\xxx\Backup\’
    set @rowcount = ‘0’
    set @mirroringmode = ‘Off’
    set @newmirror = ‘1’

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.