Backup

;WITH LastFullBackup AS
(
    SELECT 
        bs.database_name,
        MAX(bs.backup_finish_date) AS LastFullBackupDate
    FROM msdb.dbo.backupset bs
    WHERE bs.type = 'D'   -- Full Backup
    GROUP BY bs.database_name
)
SELECT  
    SERVERPROPERTY('MachineName') AS ServerName,
    d.name AS DatabaseName,
    'Full' AS BackupType,
    lfb.LastFullBackupDate,
    bmf.physical_device_name AS PhysicalDeviceName
FROM sys.databases d
LEFT JOIN LastFullBackup lfb 
    ON d.name = lfb.database_name
LEFT JOIN msdb.dbo.backupset bs
    ON lfb.database_name = bs.database_name
   AND lfb.LastFullBackupDate = bs.backup_finish_date
   AND bs.type = 'D'
LEFT JOIN msdb.dbo.backupmediafamily bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE d.database_id > 4   -- Exclude system databases (master, model, msdb, tempdb)
  AND (
        lfb.LastFullBackupDate IS NULL 
        OR lfb.LastFullBackupDate < DATEADD(DAY, -7, GETDATE())
      )
  AND (
        d.replica_id IS NULL -- Standalone DB
        OR EXISTS (  -- If Always On, must be PRIMARY replica
            SELECT 1 
            FROM sys.dm_hadr_database_replica_states drs
            JOIN sys.availability_replicas ar 
                ON drs.replica_id = ar.replica_id
            WHERE drs.database_id = d.database_id
              AND drs.is_local = 1
              AND drs.synchronization_state_desc = 'SYNCHRONIZED'
              AND drs.is_primary_replica = 1
        )
      )
ORDER BY d.name;