Quickly Flip Database to Mirroring Partner

While Database Mirroring is certainly not a new technology, I’ve only started using it for HA on some of our older SQL instances. In doing so, I’ve found the need to quickly (and manually) flip the databases using mirroring to the mirrored instance. While you can accomplish this slowly using the GUI, if you have more than 1 or 2 databases to flip it can get old very quickly.

With that in mind, I’ve created a script where the output of the script can be used to quickly flip the databases that meet certain criteria. In this case, any databases that are currently SYNCHRONIZED and are the PRINCIPAL in the mirroring session will be selected.

   1: SELECT 'ALTER DATABASE [' + DB_NAME(database_mirroring.database_id) + '] SET PARTNER FAILOVER' AS 'Script'

   2:        ,DB_NAME(database_mirroring.database_id) as 'DB Name'

   3:        ,database_mirroring.mirroring_partner_name AS 'Mirroring Failover Partner'

   4: FROM    sys.database_mirroring

   5:  WHERE database_mirroring.mirroring_state_desc = 'SYNCHRONIZED'

   6:    AND database_mirroring.mirroring_role_desc = 'PRINCIPAL'

The meat of the script is in column 1. By running the commands generated in column 1 in another window with the same connection, you can quickly flip the databases to the mirrored servers. Columns 2 and 3 are there for extra information, namely the name of the database to be flipped and the name of the mirrored server that will serve primary instance of the database.

Enjoy!