TSQL to report DB Mirroring status and configuration

Have you been faced with the need to report on, or verify the configuration of your SQL Server Database Mirroring. Well maybe you have or maybe you haven't, if you only have a small number of databases with DB Mirroring then this may not be very useful, but for me with 50-100 databases with DB Mirroring I needed a way to quickly check what the status and safety level of the mirrored databases were. Not to mention the fact that I needed to easily identify the databases that were no mirrored to report back to the application owners so they knew what was not in a DR configuration.

Hopeful this query can save you some time or at least give you a starting point if facing a similar situation:

SELECT a.name
, ISNULL(b.mirroring_role_desc, 'NOT MIRRORED') as mirroring_role_desc
, ISNULL(b.mirroring_state_desc, '') as mirroring_state_desc
, ISNULL(mirroring_safety_level_desc, '') as mirroring_safety_level_desc
FROM sys.databases a
INNER JOIN sys.database_mirroring b on b.database_id = a.database_id
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
ORDER BY a.name