t-sql to check db not recently backed up (and if db is in an AG, then only care about primary replica)

• doodlingdba

  Hall of Fame

  Points: 3164

  More actions

  March 20, 2018 at 4:09 pm

  #343981

  hi all
  this is a t-sql question but requires some alwayson knowledge so not sure which forum is best to place my question.
  i have a daily check that checks for databases not currently (or ever) backed up. Works well. We have no introduced alwayson availabaility groups and i notice that my daily check returns databases not currently or ever backed up on my secondary replica, which i do not care about. I only want to know about databases not backed up on my primary replica. So i have some logic which checks if the database is a primary replica, but the problem is, now the script only tells me about databases that are configured in an AG - however i also want to know about databases that aren't configured in an AG. Can't quite get my head around the logic (t-sql not my strong point so bear with me).

  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 ignore databases that are NOT in the availability group. This is not what i want. I want to know about all databases that are not being backed up, and if they happen to be in an AG, only show me those if they are on a primary replica.

  Hope it's clear and someone can help - my brain is frazzled!

  Thanks
  Doodles

• IHall

  Valued Member

  Points: 53

  More actions

  April 13, 2018 at 7:01 am

  #1986740

  Hi,

  You could left join to the sys.dm_hadr_database_replica_states view and interrogate the is_primary_replica column. This view only returns rows for database that participate in an always on availability group therefore the query will return primary replicas or non AG databases (hence the null check).

  Sorry not been able to test as I don't have AO set up anywhere so might need tweaking.
  
  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
  /* Here is the new left join */
   LEFT JOIN sys.dm_hadr_database_replica_states r
            ON r.database_id = d.database_id
  WHERE (
      f.BackupFinishDate IS NULL -- never backedup
      OR
      F.BackupFinishDate < GETDATE()
    )
   AND d.name not in ('tempdb', 'ReportServerTempDb', 'pubs','northwind', 'AdventureWorks')
  AND d.state_desc = 'ONLINE'
 /* Here is the check */ 
 AND (r.is_primary_replica = 1 OR r.group_id IS NULL) order BY d.name;