Once you start working on instances with tens or hundreds of mirrored databases, you’ll need scripts to manage the databases.

Below are common scripts which can help:

Check which databases have mirroring configured
Check which databases do not have mirroring configured
Check which mirrored databases are in synchronous mode
Check which mirrored databases are not in synchronous mode
Check which mirrored databases are in asynchronous mode
Check which mirrored databases are not in asynchronous mode
Check which mirrored databases are synchronized
Check which mirrored databases are not synchronized
Check which mirrored databases have mirroring paused
Check which mirrored databases do not have mirroring paused
Check what the mirroring ping timeout value is for the mirrored databases
Change mirrored databases to asynchronous mode
Change mirrored databases to synchronous mode
Change the mirroring ping timeout value for the mirrored databases
Pause mirroring for mirrored databases
Resume mirroring for mirrored databases
Manually failover mirrored databases
Force recovery of mirrored databases in the event the principal instance is not available
Remove database mirroring from mirrored databases

Check which databases have mirroring configured

This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_have_mirroring_configured
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

Check which databases do not have mirroring configured

This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_do_not_have_mirroring_configured
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NULL
AND DB_NAME(database_id) NOT IN ('master','model','msdb','tempdb')
ORDER BY DB_NAME(database_id);

Check which mirrored databases are in synchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_are_in_synchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are not in synchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_databases_are_not_in_synchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc <> 'FULL'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are in asynchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_mirrored_databases_are_in_asynchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are not in asynchronous mode

Only Enterprise Edition allows asynchronous mode.
This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS these_mirrored_databases_are_not_in_asynchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc <> 'OFF'
ORDER BY DB_NAME(database_id);

Check which mirrored databases are synchronized

This can be run on either the principal or mirror instance.

SELECT 
 DB_NAME(database_id) AS these_databases_are_fully_synchronized
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'SYNCHRONIZED' 
ORDER BY DB_NAME(database_id);

Check which mirrored databases are not synchronized

This can be run on either the principal or mirror instance.

SELECT 
 DB_NAME(database_id) AS these_databases_are_not_fully_synchronized
 ,mirroring_state_desc 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc <> 'SYNCHRONIZED' 
ORDER BY DB_NAME(database_id);

Check which mirrored databases have mirroring paused

This can be run on either the principal or mirror instance.

SELECT DB_NAME( database_id ) AS these_databases_have_mirroring_paused
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'SUSPENDED'
ORDER BY DB_NAME( database_id );

Check which mirrored databases do not have mirroring paused

This can be run on either the principal or mirror instance.

SELECT DB_NAME( database_id ) AS these_databases_do_not_have_mirroring_paused
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc <> 'SUSPENDED'
ORDER BY DB_NAME( database_id );

Check what the mirroring ping timeout value is for the mirrored databases

This can be run on either the principal or mirror instance.

SELECT DB_NAME(database_id) AS mirrored_database
,mirroring_connection_timeout AS mirroring_connection_timeout_value_in_seconds
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

Change mirrored databases to asynchronous mode

Only Enterprise Edition allows asynchronous mode
Run on which ever instance contains the principal database(s) you would like to have asynchronous mirroring mode set.

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to asynchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

Change mirrored databases to synchronous mode

Run on which ever instance contains the principal database(s) you would like to have synchronous mirroring mode set.

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY FULL;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to synchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

Change the mirroring ping timeout value for the mirrored databases

Run on which ever instance contains the principal database(s) you would like to have the mirroring ping timeout value changed.

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER TIMEOUT 90;'
+ ' PRINT ''The mirroring ping timeout value for [' +  DB_NAME(database_id) + '] has been changed.'';'
  AS command_to_change_the_mirroring_ping_timeout_value_for_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
ORDER BY DB_NAME(database_id);

Pause mirroring for mirrored databases

Run on which ever instance contains the principal database(s) you would like to have mirroring paused.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER SUSPEND;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring paused.'';' 
  AS command_to_pause_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc <> 'SUSPENDED'
ORDER BY DB_NAME(database_id);

Resume mirroring for mirrored databases

Run on which ever instance contains the principal database(s) you would like to have mirroring resumed.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER RESUME;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring resumed.'';' 
  AS command_to_resume_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc = 'SUSPENDED'
ORDER BY DB_NAME(database_id);

Manually failover mirrored databases

Run on which ever instance contains the principal database(s) you want to manually failover.
Make sure you are in the master database, all mirrored databases to failover are fully synchronized and not paused.

SELECT 
 'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FAILOVER;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been been manually failed over.'';' 
  AS command_to_manually_failover_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
AND mirroring_state_desc = 'SYNCHRONIZED'
ORDER BY DB_NAME(database_id);

Force recovery of mirrored databases in the event the principal instance is not available

Run on which ever instance contains the mirrored database(s) you want to recover.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been recovered and mirroring suspended. Restart the principal instance when it becomes available and resume mirroring, or remove mirroring from the principal and mirror instances.'';' 
  AS command_to_force_recovery_of_a_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'DISCONNECTED'
ORDER BY DB_NAME(database_id);

Remove database mirroring from mirrored databases

This can be run on either the principal or mirror instance that contains the database(s) you want mirroring removed.

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring removed.'';' 
  AS command_to_remove_mirroring_from_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);