How to List all Mirrored SQL Server Databases

To list all databases in a SQL Server instance with the information if the database is configured for mirroring use the following query:

SELECT d.name AS Database_Name
,CASE
WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
ELSE 'Mirrored'
END AS Mirroring_Status
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
ORDER BY d.name

Here is a similar query with an added information about the mirroring state, that can for the mirrored databases be one of the following:
DISCONNECTED, SYNCHRONIZED, SYNCHRONIZING, PENDING_FAILOVER, SUSPENDED, UNSYNCHRONIZED.

SELECT d.name AS Database_Name
,CASE
WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
ELSE 'Mirrored'
END AS Mirroring_Status
, dm.mirroring_state_desc as Mirroring_State
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
ORDER BY d.name

To list all mirrored databases in a SQL Server instance:

SELECT d.name AS Database_Name
,CASE
WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
ELSE 'Mirrored'
END AS Mirroring_Status
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
WHERE dm.mirroring_state IS NOT NULL
ORDER BY d.name

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">