Check whether databases are configured for Database Mirroring

On 16 Apr, 2015

MS SQL DBA

By : Charith Silva

No Comments

Below script can be used to check if the databases are configured for Database Mirroring for a given SQL Server instance. This information can be pulled using SQL Server Management Studio (See below the steps), but if you have lots of databases it might takes very long time to check.

Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

USE [master];
GO

SELECT @@SERVERNAME as [Server Name],
d.name As [Databse Name],
CASE
    WHEN m.mirroring_state is NULL THEN 'Not Mirrored'
    ELSE 'Mirrored'
END as [Status],
m.mirroring_state_desc as [State of the mirrored database],
m.mirroring_role_desc as [Current role in the database mirroring session],
m.mirroring_witness_state_desc as [State of the witness in the database mirroring session]
FROM sys.databases d
INNER JOIN sys.database_mirroring m
ON d.database_id=m.database_id
WHERE d.database_id > 4
ORDER BY d.NAME

USE [master];

SELECT @@SERVERNAME as [Server Name],

d.name As [Databse Name],

CASE

WHEN m.mirroring_state is NULL THEN 'Not Mirrored'

ELSE 'Mirrored'

END as [Status],

m.mirroring_state_desc as [State of the mirrored database],

m.mirroring_role_desc as [Current role in the database mirroring session],

m.mirroring_witness_state_desc as [State of the witness in the database mirroring session]

FROM sys.databases d

INNER JOIN sys.database_mirroring m

ON d.database_id=m.database_id

WHERE d.database_id > 4

ORDER BY d.NAME

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva