SQL SERVER – How to Break Mirroring?

SET PARTNER OFF can break mirroring by permanently removing its relationship. I confirm the intended outcome before either command.

SELECT DB_NAME(database_id) AS DatabaseName,mirroring_state_desc,
       mirroring_role_desc,mirroring_partner_name FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
-- Planned removal on the verified principal or mirror:
-- ALTER DATABASE [YourDatabase] SET PARTNER OFF;
-- On the former mirror only, when recovery is deliberately intended:
-- RESTORE DATABASE [YourDatabase] WITH RECOVERY;

Confirm database identity, roles and synchronization state first. Each instance retains a separate copy. The former mirror normally remains RESTORING.

WITH RECOVERY can make that copy available when approved. It starts a new recovery fork and ends its restore sequence. It is not a universal second command for either server. Additional restores or rebuilding mirroring need a new plan.

Preserve recoverable backups and coordinate application routing. Avoid independently writable copies competing as the authority. Mirroring is deprecated, so assess a supported design for new deployments. The commands remain disabled administrative templates.

Reference: Mirroring removal and recovery consequences.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds series
• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Removing mirroring is not pausing synchronization, it is a permanent relationship change that needs a recovery plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.