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,

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