t-sql to report on databases not recently backed up - if db is in an availability group, only report if it's a primary replica

  • Hi all
    This is a t-sql question but also involves some knowledge of the AlwaysOn DMVs so wasn't sure where to best to post this. I have a daily check that reports on databases that have not been backed up within a certain time frame (or have never been backed up). Works well. We have now introduced alwayson availability groups and my daily check is telling me about databases not recently backed up even on my secondary replica - which i don't care about.

    Here's my code: 

    DECLARE @MyDate DATETIME = '20 March 2018';
    WITH LastBackupCTE (DbName, BackupfinishDate)
    AS
    (
        SELECT database_name,
                MAX(backup_finish_date) AS BackupFinishDate
                    FROM msdb.dbo.backupset bs
                inner join msdb.dbo.backupmediafamily bmf
                    on bs.media_set_id = bmf.media_set_id
            WHERE type = 'D' -- database backup.
            GROUP BY database_name
    )
    SELECT d.name
         FROM sys.databases d
            LEFT OUTER JOIN LastBackupCTE f
                ON d.name = f.DbName
        WHERE (
                    f.BackupFinishDate IS NULL -- never backedup
                    OR
                    F.BackupFinishDate < @MyDate
                )
            AND d.name not in ('tempdb', 'ReportServerTempDb', 'pubs','northwind', 'AdventureWorks')
       AND d.state_desc = 'ONLINE'
            AND (sys.fn_hadr_is_primary_replica(d.name)) = 1
            
        order BY d.name;
     

    Currently this will report backup any databases not backed up recently (or ever) but only if they are part of an availability group. It will ignore databases which are NOT in an availability group, which is not what i want. I want to know about all databases, and if they are in an availability group, only tell me about those on the primary replica. T-sql isn't my speciality so bear with me.

    Any help would be appreciated!

    Thanks
    Doodles

  • Try dmv sys.dm_hadr_database_replica_states and just join them:

    DECLARE @MyDate DATETIME = '21 March 2018';
    WITH LastBackupCTE (DbName, BackupfinishDate)
    AS
    (
      SELECT database_name,
        MAX(backup_finish_date) AS BackupFinishDate
          FROM msdb.dbo.backupset bs
        inner join msdb.dbo.backupmediafamily bmf
          on bs.media_set_id = bmf.media_set_id
       WHERE type = 'D' -- database backup.
       GROUP BY database_name
    )
    SELECT d.name
      FROM sys.databases d
         left outer join sys.dm_hadr_database_replica_states ha on d.database_id=ha.database_id
       LEFT OUTER JOIN LastBackupCTE f
        ON d.name = f.DbName
      WHERE (
          f.BackupFinishDate IS NULL -- never backedup
          OR
          F.BackupFinishDate < @MyDate
        )
       AND d.name not in ('tempdb', 'ReportServerTempDb', 'pubs','northwind', 'AdventureWorks')
      AND d.state_desc = 'ONLINE' and (ha.is_primary_replica=1 or ha.is_primary_replica is null)
       --AND (sys.fn_hadr_is_primary_replica(d.name)) = 1
      
      order BY d.name;

  • That's awesome! And so simple... sometimes the simple things are so elusive 🙂

    Thanks for your quick response!

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

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