Generate Mirroring Setup Scripts

/********************************************************************************************

* Purpose: Generate steps to setup mirroring in SQL 2005 *

* Author: Farhan F. Sabzaali *

* Date Created: March 17, 2009 *

* Last Updated: *

* Updates Made: *

* Instructions: 1. Modify section marked "CHANGE ME" *

* 2. Run Query - Change Result to Text as output *

* 3. Execute one step at a time *

********************************************************************************************/

DECLARE

@DatabaseName NVARCHAR (250)

,@Principal NVARCHAR (1000)

,@Mirror NVARCHAR (1000)

,@DomainAccount NVARCHAR (500)

,@DefaultLanguage NVARCHAR (250)

,@ListenerPort NVARCHAR (50)

,@PrincipalBackupFolder NVARCHAR (1000)

,@DatabaseDataFileName NVARCHAR (250)

,@MirrorDataPath NVARCHAR (1000)

,@MirrorLogPath NVARCHAR (1000)

,@PrincipalTCP NVARCHAR (1000)

,@MirrorTCP NVARCHAR (1000)

,@MirrorSafety NVARCHAR (50)

,@MirrorMonitorJobName NVARCHAR (250)

,@DBDeleteDate NVARCHAR (100)

,@Step1 NVARCHAR (MAX)

,@Step2 NVARCHAR (MAX)

,@Step3 NVARCHAR (MAX)

,@Step4 NVARCHAR (MAX)

,@Step5 NVARCHAR (MAX)

,@Step6 NVARCHAR (MAX)

,@Step7 NVARCHAR (MAX)

,@Step8 NVARCHAR (MAX)

,@Step9 NVARCHAR (MAX)

,@AllSteps NVARCHAR (MAX)

,@NewLine CHAR (1)

,@APOS CHAR (1)

/* CHANGE ME START */

SET @DatabaseName = 'AdventureWorks' --Database Name

SET @DatabaseDataFileName = 'AdventureWorks_Data' --Logical Name of Data File

SET @Principal = 'SERVERA\INSTANCE1' --Principal Server \ Instance Name

SET @Mirror = 'SERVERB\INSTANCE1' --Mirror Server \ Instance Name

SET @DomainAccount = 'Domain\SQLAGENT' --Domain Account used to setup mirror - should have access on both

SET @DefaultLanguage = '[us_english]' --Default Language

SET @ListenerPort = '5512' --Default TCP \ Mirror Listening Port

SET @PrincipalBackupFolder = '\\ServerA\Backup_Instance1\' --Shared Drive on Principal that can be accessed by Mirror

SET @MirrorDataPath = 'M:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' --Folder on Mirror where Data files are saved

SET @MirrorLogPath = 'N:\Microsoft SQL Server\MSSQL.1\MSSQL\LOGS\' --Folder on Mirror where Log files are saved

SET @PrincipalTCP = 'TCP://ServerA.corporate.domain.com:5512' --Principal TCP Configuration - Refer to BOL for details

SET @MirrorTCP = 'TCP://ServerB.corporate.domain.com:5512' --Mirror TCP Configuration - Refer to BOL for details

SET @MirrorSafety = 'FULL' --Mirror Safety - FULL OR OFF

/* CHANGE ME FINISH */

SET @MirrorMonitorJobName = 'Database Mirroring Monitor Job'

SET @NewLine = CHAR(13)

SET @APOS = CHAR (39)

SET @DBDeleteDate = CONVERT(NVARCHAR (100), GETDDATE() - 1, 101)

SET @Step1 = ''

SET @Step2 = ''

SET @Step3 = ''

SET @Step4 = ''

SET @Step5 = ''

SET @Step6 = ''

SET @Step7 = ''

SET @Step8 = ''

SET @Step9 = ''

SET @AllSteps = ''

SET @Step1 = @Step1 + '--SQL Server Mirror Setup Script - Step 1: Add Login For EndPoint'

SET @Step1 = @Step1 + @NewLine + '--Run On Principal Server'

SET @Step1 = @Step1 + @NewLine + @NewLine + '--Step 1 Start'

SET @Step1 = @Step1 + @NewLine + '

USE [Master];

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name=' + @APOS + @DomainAccount + @APOS + ')

BEGIN

CREATE LOGIN [' + @DomainAccount + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master],DEFAULT_LANGUAGE=' + @DefaultLanguage + ';

PRINT ' + @APOS + 'Create Windows login [' + @DomainAccount + '].' + @APOS + '

END

GO

IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name=' + @APOS + 'MirrorEndpoint' + @APOS + ')

BEGIN

CREATE ENDPOINT MirrorEndpoint AUTHORIZATION [' + @DomainAccount + ']

STATE=STARTED

AS TCP

(

LISTENER_PORT=' + @ListenerPort + ',

LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING

(

AUTHENTICATION = WINDOWS NEGOTIATE,

ENCRYPTION = SUPPORTED,

ROLE=ALL

);

PRINT ' + @APOS + 'Create mirroring endpoint [MirrorEndpoint].' + @APOS + '

END

GO'

SET @Step1 = @Step1 + @NewLine + @NewLine + '--Step 1 Finish'

SET @Step2 = @Step2 + '--SQL Server Mirror Setup Script - Step 2: Check Database Recovery Mode'

SET @Step2 = @Step2 + @NewLine + '--Run On Principal Server'

SET @Step2 = @Step2 + @NewLine + @NewLine + '--Step 2 Start'

SET @Step2 = @Step2 + @NewLine + '

USE [Master];

GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = ' + @APOS + @DatabaseName + @APOS + ' AND recovery_model = 1)

BEGIN

ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY FULL;

PRINT ' + @APOS + 'Set full recovery for database [' + @DatabaseName + '].' + @APOS + '

END

GO'

SET @Step2 = @Step2 + @NewLine + @NewLine + '--Step 2 Finish'

SET @Step3 = @Step3 + '--SQL Server Mirror Setup Script - Step 3: Backup Database'

SET @Step3 = @Step3 + @NewLine + '--Run On Principal Server'

SET @Step3 = @Step3 + @NewLine + @NewLine + '--Step 3 Start'

SET @Step3 = @Step3 + @NewLine + '

USE [Master];

GO

BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.bak' + @APOS + ' WITH DESCRIPTION = ' + @APOS + 'Back up ' + @DatabaseName + ' database Full' + @APOS + ', FORMAT, INIT, NAME = ' + @APOS + @DatabaseName +'_Step3_Principal.bak' + @APOS + ', SKIP, REWIND, NOUNLOAD, STATS = 10

GO

BACKUP LOG [' + @DatabaseName + '] TO DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.trn' + @APOS + ' WITH DESCRIPTION = ' + @APOS + 'Back up ' + @DatabaseName + ' database Log' + @APOS + ', FORMAT, INIT, NAME = ' + @APOS + @DatabaseName +'_Step3_Principal.trn' + @APOS + ', SKIP, REWIND, NOUNLOAD, STATS = 10

GO'

SET @Step3 = @Step3 + @NewLine + @NewLine + '--Step 3 Finish'

SET @Step4 = @Step4 + '--SQL Server Mirror Setup Script - Step 4: Restore Database'

SET @Step4 = @Step4 + @NewLine + '--Run On Mirror Server'

SET @Step4 = @Step4 + @NewLine + @NewLine + '--Step 4 Start'

SET @Step4 = @Step4 + @NewLine + '

USE [Master];

GO

RESTORE DATABASE [' + @DatabaseName + '] FROM DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.bak' + @APOS + ' WITH MOVE ' + @APOS + @DatabaseDataFileName + @APOS + ' TO ' + @APOS + @MirrorDataPath + @Databasename + '.mdf' + @APOS + ', MOVE ' + @APOS + @DatabaseName + '_Log' + @APOS + ' TO ' + @APOS + @MirrorLogPath + @DatabaseName + '.ldf' + @APOS + ', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

GO

RESTORE LOG [' + @DatabaseName + '] FROM DISK = ' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.trn' + @APOS + ' WITH NORECOVERY, NOUNLOAD, STATS = 10

GO'

SET @Step4 = @Step4 + @NewLine + @NewLine + '--Step 4 Finish'

SET @Step5 = @Step5 + '--SQL Server Mirror Setup Script - Step 5: Setup Partner Information On Mirror'

SET @Step5 = @Step5 + @NewLine + '--Run On Mirror Server'

SET @Step5 = @Step5 + @NewLine + @NewLine + '--Step 5 Start'

SET @Step5 = @Step5 + @NewLine + '

USE [Master];

GO

ALTER DATABASE ' + @DatabaseName + '

SET PARTNER = ' + @APOS + @PrincipalTCP + @APOS + '

GO'

SET @Step5 = @Step5 + @NewLine + @NewLine + '--Step 5 Finish'

SET @Step6 = @Step6 + '--SQL Server Mirror Setup Script - Step 6: Setup Partner Information On Principal'

SET @Step6 = @Step6 + @NewLine + '--Run On Principal Server'

SET @Step6 = @Step6 + @NewLine + @NewLine + '--Step 6 Start'

SET @Step6 = @Step6 + @NewLine + '

USE [Master];

GO

ALTER DATABASE ' + @DatabaseName + '

SET PARTNER = ' + @APOS + @MirrorTCP + @APOS + '

GO

ALTER DATABASE ' + @DatabaseName + ' SET SAFETY ' + @MirrorSafety + ';

GO

USE [msdb];

GO

IF EXISTS (SELECT * FROM dbo.sysjobs WHERE name LIKE ' + @APOS + @MirrorMonitorJobName + @APOS + ')

BEGIN

PRINT ' + @APOS + @MirrorMonitorJobName + ' already exists on server instance [' + @Principal + '].' + @APOS + ';

END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysjobs WHERE name LIKE ' + @APOS + @MirrorMonitorJobName + @APOS + ')

BEGIN

EXECUTE sp_dbmmonitoraddmonitoring;

PRINT ' + @APOS + @MirrorMonitorJobName + ' has been added to server instance [' + @Principal + '].' + @APOS + ';

END

GO'

SET @Step6 = @Step6 + @NewLine + @NewLine + '--Step 6 Finish'

SET @Step7 = @Step7 + '--SQL Server Mirror Setup Script - Step 7: Verify Principal Setup'

SET @Step7 = @Step7 + @NewLine + '--Run On Principal Server'

SET @Step7 = @Step7 + @NewLine + @NewLine + '--Step 7 Start'

SET @Step7 = @Step7 + @NewLine + '

USE [Master];

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name=' + @APOS + @DomainAccount + @APOS + ')

PRINT ' + @APOS + 'Windows Login [' + @DomainAccount + '] does not exist.' + @APOS + '

GO

IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name=N''MirrorEndpoint'' AND type=4)

PRINT ' + @APOS + 'Mirroring endpoint [MirrorEndpoint] does not exist.' + @APOS + '

GO

IF NOT EXISTS (SELECT * FROM sys.database_mirroring A INNER JOIN sys.databases B ON A.database_id = B.database_id WHERE B.name=' + @APOS + @DatabaseName + @APOS + ' AND A.mirroring_state=4 AND A.mirroring_role=1 AND A.mirroring_partner_instance=' + @APOS + @Mirror + @APOS + ')

PRINT ' + @APOS + 'Principal Database [' + @DatabaseName + '] does not exist or may not be synchronized or in principal role.' + @APOS + '

GO'

SET @Step7 = @Step7 + @NewLine + @NewLine + '--Step 7 Finish'

SET @Step8 = @Step8 + '--SQL Server Mirror Setup Script - Step 8: Verify Mirror Setup'

SET @Step8 = @Step8 + @NewLine + '--Run On Mirror Server'

SET @Step8 = @Step8 + @NewLine + @NewLine + '--Step 8 Start'

SET @Step8 = @Step8 + @NewLine + '

USE [Master];

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name=' + @APOS + @DomainAccount + @APOS + ')

PRINT ' + @APOS + 'Windows Login [' + @DomainAccount + '] does not exist.' + @APOS + '

GO

IF NOT EXISTS (SELECT * FROM sys.database_mirroring_endpoints WHERE name=N''MirrorEndpoint'' AND type=4)

PRINT ' + @APOS + 'Mirroring endpoint [MirrorEndpoint] does not exist.' + @APOS + '

GO

IF NOT EXISTS (SELECT * FROM sys.database_mirroring A INNER JOIN sys.databases B ON A.database_id = B.database_id WHERE B.name=' + @APOS + @DatabaseName + @APOS + ' AND A.mirroring_state=4 AND A.mirroring_role=2 AND A.mirroring_partner_instance=' + @APOS + @Principal + @APOS + ')

PRINT ' + @APOS + 'Principal Database [' + @DatabaseName + '] does not exist or may not be synchronized or in principal role.' + @APOS + '

GO'

SET @Step8 = @Step8 + @NewLine + @NewLine + '--Step 8 Finish'

SET @Step9 = @Step9 + '--SQL Server Mirror Setup Script - Step 9: Delete Backup Files'

SET @Step9 = @Step9 + @NewLine + '--Run On Principal Server'

SET @Step9 = @Step9 + @NewLine + @NewLine + '--Step 9 Start'

SET @Step9 = @Step9 + @NewLine + '

USE [Master];

GO

EXECUTE xp_delete_file

0,

' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.bak' + @APOS + ',

' + @APOS + 'bak' + @APOS + ',

' + @APOS + @DBDeleteDate + @APOS + ',

0

GO

EXECUTE xp_delete_file

0,

' + @APOS + @PrincipalBackupFolder + @DatabaseName + '_Step3_Principal.trn' + @APOS + ',

' + @APOS + 'trn' + @APOS + ',

' + @APOS + @DBDeleteDate + @APOS + ',

0

GO'

SET @Step9 = @Step9 + @NewLine + @NewLine + '--Step 9 Finish'

SET @AllSteps = @Step1 + @NewLine + @NewLine + @Step2 + @NewLine + @NewLine + @Step3 + @NewLine + @NewLine +

@Step4 + @NewLine + @NewLine + @Step5 + @NewLine + @NewLine + @Step6 + @NewLine + @NewLine +

@Step7 + @NewLine + @NewLine + @Step8 + @NewLine + @NewLine + @Step9 + @NewLine + @NewLine

SET NOCOUNT ON

SELECT @AllSteps

SET NOCOUNT OFF

/* START OF SECOND SCRIPT */

/********************************************************************************************

* Purpose: Generate script to remove mirroring in SQL 2005 *

* Author: Farhan F. Sabzaali *

* Date Created: March 17, 2009 *

* Last Updated: *

* Updates Made: *

* Instructions: 1. Run script at server \ instance to identify all mirrored database *

*********************************************************************************************/

DECLARE @DatabaseName NVARCHAR (250)

,@SQL NVARCHAR (MAX)

,@NewLine CHAR (1)

,@APOS CHAR (1)

SET @NewLine = CHAR(13)

SET @APOS = CHAR (39)

SET @SQL = ''

DECLARE CurDatabase

CURSOR FOR

SELECT B.Name

FROM sys.database_mirroring A

INNER JOIN

sys.databases B

ON

A.database_id = B.database_id

AND

A.Mirroring_Guid IS NOT NULL

OPEN CurDatabase

FETCH NEXT FROM CurDatabase INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = @SQL + '

ALTER DATABASE ' + @DatabaseName + ' SET PARTNER OFF;

GO

RESTORE DATABASE ' + @DatabaseName + ' WITH RECOVERY;

GO'

SET @SQL = @SQL + @NEWLINE

FETCH NEXT FROM CurDatabase INTO @DatabaseName

END

CLOSE CurDatabase

DEALLOCATE CurDatabase

SET NOCOUNT ON

SELECT @SQL

SET NOCOUNT OFF