Post reply

Backup information incorrect after AAG toggle

  • August 25, 2025 at 2:35 pm

    #4640486

    Good morning all,

    I have the attached T-SQL query that has been working well for sometime. This past Saturday during patching, the AAG toggled from primary on server1 to primary on server2. Now the query does not report differentials from the current primary server. Any pointer/suggestions on what I fail to see is greatly appreciated.

    Thanks

     

    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
    Attachments:
    You must be logged in to view attached files.

  • August 25, 2025 at 7:39 pm

    #4640641

    Duplicate. Please post replies here

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply