Backup query not returning differential backup info after AAG toggles

1

Good morning all,

I have a query that returns pivoted information on all FULL and Differential backups for my AAG SQL instances that works (so I thought) very well, but when the AAG toggles after the FULL backup is taken, the next day the query fails to pull the differential information. In short server1 primary had a full backup taken on Friday night by NetBackup then on Saturday the AAG toggles now having server2 as primary and NetBackup successfully performs a differential backup on it. My query though does not report on that backup having taken place. Attached is the code for your review, looking to see what I’m doing wrong. Thanks for guidance with this

SELECT

    @@SERVERNAME,
    M.name as DBName,
    [RecoveryModel] = M.recovery_model_desc,
    [State] = M.state_desc,

    FORMAT(ISNULL(M.D, '19000101'), 'MM/dd/yyyy hh:mm') as LastFull ,
    FORMAT(ISNULL(M.I, '19000101'), 'MM/dd/yyyy hh:mm') as LastDifferential,
    M.Software as 'Backup_Software_info'

FROM

(
    SELECT

        db.name,
        db.state_desc,
        db.recovery_model_desc,
        a.type,
        a.backup_finish_date,
        a.name as 'Software'

    FROM master.sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS a
      ON a.database_name = db.name

) AS Sourcetable

PIVOT

(
    MAX(backup_finish_date)
    FOR type IN
    (
        I,
        D
    )

) AS M --MostRecentBackup

WHERE name NOT IN

( N'master', N'msdb', N'model', N'tempdb', N'STIGMonitor' )  --Ignore system databases

and
  M.D > DATEADD(DAY, -7, GETDATE())   --Look back only 7 days of FullBackup   

and
    name in (SELECT name from sys.databases)  --Active Databases

ORDER BY name