数据库故障转移/故障恢复历史记录

Question

我是SQL新手，我正在尝试获取镜像服务器的故障转移/故障恢复历史记录。我能够从事件查看器获得故障转移历史记录，但不知道如何获得故障恢复历史记录。非常感谢您的帮助！

Answer 1

下面是我为显示故障转移和故障回退历史而编写的脚本：

SELECT d.name, t3.time, (1 - t3.role) as status
FROM (
    SELECT t1.database_id, t1.time, t1.role 
    FROM 
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time 
            FROM msdb.dbo.dbm_monitor_data dbmd 
        ) as t1
        INNER JOIN 
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time 
            FROM msdb.dbo.dbm_monitor_data dbmd 
        ) t2 ON T1.database_id = T2.database_id and t1.rec = (t2.rec - 1) AND t1.role <> t2.role
    ) t3
    INNER JOIN sys.databases d ON t3.database_id = d.database_id
ORDER BY d.name, t3.time

我相信Role = 0是PRIMARY。