Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 9 June 2017

How to break and resume Database Mirroring on SQL Server


To know the status:

SELECT (SELECT DB_NAME())AS DBName,database_id,mirroring_state_desc,
mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=20






To break database Mirroring:

-------------Run it on Principal

ALTER DATABASE TestDBMirror SET PARTNER OFF


Now you can database mirroring is broken


To Presume database Mirroring:

Now we should re-establish mirroring between the servers for this database. First, add the partner server on the mirror server. The below SQL code will add the principal server on the mirror server.

--------------to presume we need to run it on mirror

ALTER DATABASE TestDBMirror SET PARTNER ='TCP://USER-PC.LOCAL:5022'


Now add the partner server on the principal. Run the below SQL code to add the mirror server to the principal server to resume mirroring.

----------second now it run on Principal

ALTER DATABASE TestDBMirror SET PARTNER ='TCP://USER-PC.LOCAL:5023'


Now we need to refresh it on GUI as see below

it will resume DATABASE MIRRORING