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.  

	/*Create endpoints on both servers*/
	CREATE ENDPOINT EndPointName
	STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
	FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
	/*Set partner and setup job on mirror server*/
	ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
	EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
	/*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 OFF
	EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
	/*FAILOVER */
	ALTER DATABASE <database_name> SET PARTNER FAILOVER
	ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

view raw SQLTechNet - Mirroring.sql hosted with ❤ by GitHub