Wednesday, 1 August 2012

Set Up SQL Server Mirroring T-SQL Script

The first thing you need to do when setting up Database Mirroring is perform a full backup followed by a transaction log backup on the principal server.  You then must restore these to the mirror server using the WITH NORECOVERY option of the RESTORE command.  

1 comment:

  1. Thanks great and very short article.
    I was facing issue while doing failover.
    [error msg:-The database mirroring service cannot be forced...]
    I would suggest to keep safety ON to avoid such error.
    Please refer below code.

    /*Set partner, set asynchronous mode, and setup job on principal server*/

    ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
    ALTER DATABASE DatabaseName SET SAFETY ON --use ON instead of OFF.
    EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute