Couple of days back one of my team members wanted to get the database list which were configured for Database Mirroring on a given instance. Since I did not have a script readily available, I started putting together one for him.
The easiest approach is to query the sys.database_mirroring DMV. The output of this DMV has a column named mirroring_state, which has different values for the Mirroring State. This column will have a value of NULL, if a database is not mirrored or is inaccessible. Based on this logic, I wrote the following script to check the mirroring status of all the databases on a given instance.
SELECT A.name, CASE WHEN B.mirroring_state is NULL THEN 'Mirroring not configured' ELSE 'Mirroring configured' END as MirroringState FROM sys.databases A INNER JOIN sys.database_mirroring B ON A.database_id=B.database_id WHERE a.database_id > 4 ORDER BY A.NAME
The output of this script is as follows.
Do you have a better approach to this requirement? Please feel free to share.