SQL 2005 Disaster Recovery Scripts: To Setup and Remove Mirroring
SQL 2005 includes a very easy to use method to setup and remove mirroring using SQL Server Management Studio however, when you are dealing with 30+ databases spread across multiple instances, it becomes tedious to use GUI to setup and remove mirroring.
I have included two scripts that may assist you in performing the task using T-SQL as follows:
1. Setup mirroring: This script generates nine (9) distinct steps using information you provide for a single database - these steps can then be used to save scripts to quickly setup mirroring logic for each database. I have tried to include detailed comments on what each step does and section on what information you need to have available to generate the final script (see script for further details). Note: I have not included piece for Witness.
2. Remove mirroring: This script generates manual failover script for all databases per server \ instance that are mirrored. Note: This does not actually do failover but just generates the script that you can then use to do failover.
Hope this helps you in your day-to-day job function or during disaster recovery exercise. Would love to hear of suggestions to improve these.
Thanks,
Farhan F. Sabzaali
/********************************************************************************************
* 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), GETDATE() - 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
