Cool Tips‎ > ‎Database Mirroring‎ > ‎

Database Mirroring - Generate Mirroring Setup Scripts

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Not Tested
                 SQL Server 2008:        Not Tested
                 SQL Server 2008R2:    Not Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Farhan F Sabzaali
Date:        17 mar 2009

Description

This will be one of the few scripts that I publish without first testing as I don't currently have a server setup on which I can perform the tests, but visually the scripts look fairly complete.
The instructions within the scripts are comprehensive, and there is a recommendation to run the resulting TSQL step-by-step, so any errors would immediately be highlighted.

NOTE:  Running the scripts below makes no changes to the SQL Server.

Code


 

   /********************************************************************************************
    *    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

ċ
Generate scripts to add or remove DB mirroring.sql
(32k)
Andy Hughes,
Jul 19, 2012, 8:20 AM
Comments