How to Scan and Get Mirrored Databases in SQL Server

For setting a SQL Server database mirroring we need two SQL Server instance. These two SQL instances can be on the same machine or on different machine. In this article, we will learn how to get the list of all mirrored databases from our SQL Server environment.

Scan for Always On

Mirrored DatabasesThis script would help you to scan your SQL environment and identify SQL Server databases that are part of mirroring. In this script, we would be connecting to each linked server from a Central server. Please do note that this script will not create any linked server. Before executing this script, please make sure that you have created linked servers. As a first step, we would be loading name of all linked servers in a table.

From this table, using a cursor we would be fetching each server name and then prepare the script. This script would be stored in the second table. We would then use another cursor to fetch the command and execute it on each linked server. The result set is stored in the final table.

Key tables

This script fetches data from two tables. The first one is sys.databases and the other one is sys.database_mirroring.

The table sys.database_mirroring would return 21 fields for each database on the server. If the database is not a part of mirroring or if it is offline, then the output in sys.database_mirroring for that database would be NULL for all fields except the database id.

DECLARE @Tserver TABLE 
     ( 
         cserver VARCHAR(200) 
     )

INSERT INTO @Tserver 
VALUES ('ECPLL027\NI2014')

DECLARE @Tcmd TABLE 
     ( 
         ccmd VARCHAR(2000) 
     ) 
DECLARE @Tlog TABLE 
     ( 
         p_server VARCHAR(200), 
         p_dbname varchar(200) 
     )

INSERT INTO @Tcmd 
SELECT 'exec (''SELECT @@servername, 
A.name
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE a.database_id > 4 and B.mirroring_state is not null
ORDER BY A.NAME 
'') at [' 
       + cserver + ']' 
FROM @Tserver

DECLARE c1 CURSOR FOR 
     SELECT * 
     FROM @Tcmd 
DECLARE @cmd VARCHAR(2000)

OPEN c1

FETCH next FROM c1 INTO @cmd

WHILE @@FETCH_STATUS = 0 
     BEGIN 
         PRINT @cmd

         INSERT INTO @Tlog 
                     (p_server,p_dbname 
                     ) 
         EXEC(@cmd)

         FETCH next FROM c1 INTO @cmd 
     END

CLOSE c1

DEALLOCATE c1

SELECT * 
FROM @Tlog

Tweak it

T-SQLThis script includes server name and database name. You can easily tweak it by adding more fields from sys_database_mirroring table. You can include mirroring_state. This field gives info about the state of mirrored database and the mirroring session. If there is 0 against this field, it indicates that the mirroring session is suspended. If there is 1 against the mirroring state it indicates that the mirroring partner is disconnected from the other; 2 indicates that the database is in synchronizing state; 3 indicates that the failover is pending; 4 indicates that the database is synchronized; 5 indicates that the mirroring partners are not synchronized and there is no possibility for the failover and 6 indicates that it is highly possible for partner’s failover. If this field shows NULL, the database is not part of mirroring. Mirroring will help you to recover quickly whenever there is SQL Server corruption

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com