Technical Article

Database Mirroring Setup Procedure generator

,

1. Copy-Paste code from the script. It will install a stored procedure. !!! Change the schema to [dbo] or create a database schema [Administration].

2. Run the procedure :

EXEC [Administration].[GenerateMirroringSetupCommands]    @NetworkSharedBackupDestination  = '\\Serv1\Backup\MSSQLSERVER',

                                                        @BackupDestination                 = 'D:\Backup\',

                                                        @PrincipalEndpoint                 = 'TCP://serv1:5022',

                                                        @MirrorEndpoint                     = 'TCP://serv1_mirror:5022',

                                                        @MirrorDataFolder                 = 'P:\',

                                                        @MirrorLogsFolder                 = 'L:\'

PRINT '--------------------------------------------------------------------------------------------------------------'
PRINT 'PROCEDURE [Administration].[GenerateMirroringSetupCommands]'

IF  NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[Administration].[GenerateMirroringSetupCommands]') AND type in (N'P'))
BEGIN
    EXECUTE ('CREATE Procedure [Administration].[GenerateMirroringSetupCommands] ( ' +
            ' @ServerName    varchar(512), ' +
            ' @DbName    varchar(50) ' +
            ') ' +
            'AS ' +
            'BEGIN ' +
            '   SELECT ''Not implemented'' ' +
            'END')

IF @@ERROR = 0
        PRINT '   PROCEDURE created.'
    ELSE
    BEGIN
        PRINT '   Error while trying to create procedure'
        RETURN
    END        
END
GO


ALTER PROCEDURE [Administration].[GenerateMirroringSetupCommands] (
    @NetworkSharedBackupDestination NVARCHAR(4000),
    @BackupDestination              NVARCHAR(4000) = NULL,
    @PrincipalEndpoint              NVARCHAR(4000),
    @MirrorEndpoint                 NVARCHAR(4000),
    @MirrorDataFolderNVARCHAR(4000),
    @MirrorLogsFolder               NVARCHAR(4000),
    @Debug                          BIT = 1
)
AS
BEGIN 

    if(@BackupDestination is null) 
    BEGIN
        
        EXEC master.dbo.xp_instance_regread
                N'HKEY_LOCAL_MACHINE',
                N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
                @BackupDestination OUTPUT, 
                'no_output'  
        PRINT 'WARNING : no Backup destination given. Taking default backup destination ' + @BackupDestination
    END
    
    if(@NetworkSharedBackupDestination is null) 
    BEGIN
        RAISERROR('No shared backup destination found.',10,1)
    END

if @Debug = 1 
BEGIN 
PRINT '-- Backup destination : ' + @BackupDestination
PRINT '-- Network share      : ' + @NetworkSharedBackupDestination
END 

    DECLARE @currentDate VARCHAR(10)
    SELECT @currentDate = CONVERT(varchar(10), GetDate(),126)

    DECLARE @LineFeed VARCHAR(10) =  CHAR(13) + CHAR(10);
    DECLARE GetProc CURSOR LOCAL
    FOR
        select
            name as DbName,
            '/*' + @LineFeed + 
            '   DbName : ' + QUOTENAME(name) + @LineFeed +
            '*/' + @LineFeed + 
            '-- On PRINCIPAL' + @LineFeed + 
            'BACKUP DATABASE ' + QUOTENAME(name) + @LineFeed +
            '    TO DISK = '''+ @BackupDestination + '\' + name + '_FULL_' + @currentDate + '.bak'''  + @LineFeed +
            '    WITH FORMAT, NAME = ''Backup for database mirroring setup'''+ @LineFeed +
            'GO' + @LineFeed + @LineFeed +
            '-- On MIRROR' + @LineFeed + 
            'RESTORE DATABASE ' + QUOTENAME(name) + @LineFeed +
            '    FROM DISK = ''' + @NetworkSharedBackupDestination + '\' + name + '_FULL_' + @currentDate + '.bak'''  + @LineFeed +
            '    WITH' + @LineFeed + 
            '{:MOVE_INSTRUCTIONS},' + @LineFeed +
            --'        {:MOVE_DATA_LOCATION},' + @LineFeed +
            --'        {:MOVE_LOGS_LOCATION},' + @LineFeed +
            '        NORECOVERY' + @LineFeed +
            'GO' + @LineFeed + @LineFeed +
            '-- On PRINCIPAL' + @LineFeed +
            'BACKUP LOG '  + QUOTENAME(name) + @LineFeed +
            '    TO DISK = '''+ @BackupDestination + '\' + name + '_LOG_' + @currentDate + '.bak'''  + @LineFeed +
            '    WITH FORMAT, NAME = ''Backup LOG for database mirroring setup'''+ @LineFeed +
            'GO' + @LineFeed + @LineFeed +
            '-- On MIRROR' + @lineFeed +
            'RESTORE LOG ' + QUOTENAME(name) + @LineFeed +
            '    FROM DISK = ''' + @NetworkSharedBackupDestination + '\' + name + '_LOG_' + @currentDate + '.bak'''  + @LineFeed +
            '    WITH' + @LineFeed + 
            '        NORECOVERY' + @LineFeed +
            'GO' + @LineFeed + @LineFeed +
            'ALTER DATABASE ' + QUOTENAME(name) + @LineFeed +
            '    SET PARTNER = '''+ @PrincipalEndPoint +'''' + @LineFeed +
            'GO' + @LineFeed + @LineFeed + 
            '-- On PRINCIPAL' + @LineFeed +
            'ALTER DATABASE ' + QUOTENAME(name) + @LineFeed +
            '    SET PARTNER = '''+ @MirrorEndpoint +'''' + @LineFeed + 
            'GO' + @LineFeed +
            'ALTER DATABASE '+ QUOTENAME(name) +' SET SAFETY OFF' + @LineFeed +
            'GO' + @LineFeed 
            
        as Procedure4DbMirror
        from sys.databases dbs
        LEFT JOIN sys.database_mirroring mDbs
        on dbs.database_id = mDbs.database_id
        where name not in ('master','msdb','model','tempdb','DBA','SAIDBA')
        AND mDbs.mirroring_guid is null
    ;

    DECLARE @CurrentDbname VARCHAR(128);
    DECLARE @CurrentProc VARCHAR(MAX);

    open GetProc ;

    FETCH NEXT from GetProc INTO @CurrentDbname, @CurrentProc;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        DECLARE GetDataFiles CURSOR LOCAL FOR
            SELECT 
                name as FileLogicalName, 
                --physical_name AS current_file_location,
                --LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1) [path], 
                REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1)) [FileName]
            FROM sys.master_files 
            where database_id = DB_ID(@CurrentDbname)
            order by type,name;
        
        DECLARE @CurrentFileName  VARCHAR(4000);
        DECLARE @NewFileDest  VARCHAR(4000) ;
        DECLARE @tmpFileMoves     VARCHAR(MAX)  ;
        DECLARE @currentFileCnt  INT;
        
        -- initializations
        SET @tmpFileMoves   = ''
        SET @currentFileCnt = 0
        
        OPEN GetDataFiles ;
        
        FETCH NEXT from GetDataFiles INTO @CurrentFileName, @NewFileDest ;
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @NewFileDest = @MirrorDataFolder + '\' + @NewFileDest 
                    
            if(@currentFileCnt > 0)
            BEGIN
                SEt @tmpFileMoves += ',' + @LineFeed ;
            END 
            SET @currentFileCnt = @currentFileCnt + 1;
            
            SET @tmpFileMoves += '        MOVE ''' + @CurrentFileName + ''' TO ''' + @NewFileDest + ''''
            
            FETCH NEXT from GetDataFiles INTO @CurrentFileName, @NewFileDest ;
        END 
        
        CLOSE GetDataFiles ;
        DEALLOCATE GetDataFiles ;

        --PRINT @CurrentProc;
        SET @CurrentProc = REPLACE(@CurrentProc,'{:MOVE_INSTRUCTIONS}',@tmpFileMoves)

        PRINT @CurrentProc;
        FETCH NEXT from GetProc INTO @CurrentDbname,@CurrentProc;
    END


    close GetProc;
    DEALLOCATE GetProc;

    PRINT '-- Execution completed.'


END 
GO

IF @@ERROR = 0
    PRINT '   PROCEDURE altered.'
ELSE
BEGIN
    PRINT '   Error while trying to alter procedure'
    RETURN
END   


PRINT '--------------------------------------------------------------------------------------------------------------'
PRINT '' 

/* 
EXEC [Administration].[GenerateMirroringSetupCommands]@NetworkSharedBackupDestination  = '\\SI-S-SERV306.st.chulg\Backup\SWISSLOGP',
@BackupDestination = 'D:\Backup\',
@PrincipalEndpoint = 'TCP://serv1:5022',
@MirrorEndpoint = 'TCP://serv1_mirror:5022',
@MirrorDataFolder = 'P:\',
@MirrorLogsFolder = 'L:\'
*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating