Tuesday, August 31, 2010

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


  1. Thanks for posting this!

  2. Un orologio replica molto elegante, questo è un grande regalo. orologi repliche Sembra molto robusto e ha un certo peso.repliche vacheron constantin orologi Ecco alcuni suggerimenti per l'acquisto di repliche e siamo sicuri che abbia un aspetto e un'alta qualità.

  3. I bought it for my husband. replica oakley sunglasses It has been hot recently. I just need a pair of sunglasses. When I see an activity, I will shoot it quickly. replica oakley lifestyle sunglasses There are not many men's sunglasses brands. Ray-Ban is my favorite. It is not fancy. The logo is also very small. It looks very representative. It is the glasses of their family. Husband is super like, happy.