Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am a SQL DBA newbie, and I am trying to get the failover/failback history for one of our mirrored servers. I am able to get the the failover history from event viewer but do not know how to the get the failback history. Your help is greatly appreciated!

share|improve this question
    
Have you tried looking at it in SSMS: technet.microsoft.com/en-us/library/ms365382.aspx – Shawn Melton Nov 12 '13 at 22:14

The following is a script I wrote for displaying the failover and failback history:

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

I believe Role = 0 is PRIMARY.

share|improve this answer
    
Update: Does not work on SQL 2012 Msg 208, Level 16, State 1, Line 1 Invalid object name 'msdb.dbo.dbm_monitor_data'. – TechnoCaveman Apr 12 at 13:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.