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
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?
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.
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’