Post reply

Backup information incorrect after AAG toggle

  • August 25, 2025 at 2:36 pm

    #4640487

    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 26, 2025 at 3:10 pm

    #4641039

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 28, 2025 at 8:55 pm

    #4642323

    The system databases are not mirrored - so any information on backups would need to be combined across all nodes in the AG to be able to see the history.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 29, 2025 at 8:12 am

    #4642527

    Secondaries cannot do differential backups either.

    So if you have failed over to a secondary and a differential backup hasn't occurred yet on the secondary, it won't report as having done one.

    Ensure all your backup jobs are configured correctly for AOAG's and that they only ever run on the primary node and the AOAG backup preference itself is set to primary only.

    But short answer is, that's to be expected until the secondary that is now primary runs a diff backup.

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

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