SQL Server Mirroring Related Queries:
How do I swap Primary and Secondary on Mirroring Databases ?
When the automatic failover is setup , the Primary / Secondary gets swapped during maintenance tasks.
To swap back the Primary/Secondary , connect to the server which’s primary and run the following command.
USE master GO ALTER DATABASE
SET PARTNER FAILOVER; GO
Mirroring Databases and the Status:
SELECT DB_NAME(database_id) AS DatabaseName , CASE WHEN mirroring_guid IS NOT NULL THEN 'Mirroring is On' ELSE 'No mirror configured' END AS IsMirrorOn , mirroring_state_desc , CASE WHEN mirroring_safety_level = 1 THEN 'High Performance' WHEN mirroring_safety_level = 2 THEN 'High Safety' ELSE NULL END AS MirrorSafety , mirroring_role_desc , mirroring_partner_instance AS MirrorServer FROM sys.database_mirroring GO
Restoring a Database which is involved in Mirroring Session
-- TURN OFF MIRRORING ALTER DATABASE <database_name> SET PARTNER OFF -- ON the mirror database RESTORE DATABASE <database_name> WITH RECOVERY;
You cannot restore a database that is involved in Mirroring without breaking/removing Mirroring.
Backup and restore of the mirror database are not allowed.
Backup of the principal database is allowed, but BACKUP LOG WITH NORECOVERY is not allowed.
Restoring the principal database is not allowed.