Home Forums SQL Server 2005 Administering Break Mirror / Restore Database / Reconfigure Mirror? RE: Break Mirror / Restore Database / Reconfigure Mirror?

  • <<The post is 6+ years old but just replying if someone ends up searching this post..! >>

    Would like to add 3 more steps

    (1) Free up the logspace for principal database involved in the mirroring and make sure it has enough space till you complete the mirroring configuration..

    (2) DISABLE any log backup schedules unless and until you restore the full+log backup on mirror otherwise if the database size is big it may take a longer time to setup the mirror and if there are more transaction log backups then you will Un-necessarilly require to load them all on mirror.

    (3) Keep an eye on logspace for principal till the mirroring is set as you are disabling it.. 🙂

    --From Principal

    ALTER DATABASE DBName SET PARTNER OFF

    --Set Recovery Mode for Principal

    ALTER DATABASE DBName SET RECOVERY FULL

    --Backup the Principal Database + Transaction Log copy over to Mirror Server C:\Mirror

    --Then restore Database to Mirror Server

    RESTORE DATABASE DBName

    FROM DISK = 'C:\Mirror\DBName.bak'

    WITH MOVE 'Release_Data' TO 'F:\SQLData\DBName.mdf',

    MOVE 'Release_log' TO 'G:\SQLLogs\DBName_log.ldf',

    REPLACE,NORECOVERY;

    GO

    --Restore Log file to Mirror

    RESTORE LOG DBName

    FROM DISK = 'C:\Mirror\DBName.trn'

    WITH NORECOVERY;

    GO

    --Set Partner on Mirror

    Alter database DBName set partner = N'TCP://x.x.x.1:####'

    --Set Partner on Master

    Alter database WinMan set partner = N'TCP://x.x.x.2:####'