Backup Status (Last 7 Days)

 SELECT sd.[name]
, sd.state_desc AS database_status
, f.backup_finish_date AS last_full_backup_date
, d.backup_finish_date AS last_diff_backup_date
, CASE WHEN (sd.database_id < 4 OR sd.recovery_model = 3) THEN 'N/A'
ELSE CONVERT (VARCHAR (20), l.backup_finish_date, 120 ) END AS last_log_backup_date
, CASE WHEN (f.backup_finish_date < (DATEADD (DAY, -7, GETDATE())) OR (f.backup_finish_date IS NULL) AND sd.name <> 'TempDB') THEN 'No full backup in the last seven days'
  WHEN (sd.name = 'TempDB') THEN 'N/A'
END AS [missing recent backup]
FROM sys.databases sd
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name ) f
ON sd.[name] = f.database_name
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'I'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
) d
ON sd.[name] = d.database_name
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'L'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
) l
on sd.[name] = l.database_name
WHERE sd.[name] <> 'tempdb'

Backup Location

SELECT DISTINCT a.database_name, a.max_backup_finish_date, d.compressed_backup_size_in_MBs, d.duration, d.[user_name], d.physical_device_name
FROM
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D' --'I for diffs and 'L' for logs
GROUP BY d.[database_name]) a
INNER JOIN
(
SELECT
 d.database_name
,d.backup_finish_date
, (compressed_backup_size/1024/1024) AS compressed_backup_size_in_MBs
, CASE
WHEN DATEDIFF (HOUR, d.backup_start_date, d.backup_finish_date) = 0
THEN CONVERT (VARCHAR (2), DATEDIFF (MINUTE, d.backup_start_date, d.backup_finish_date))+' Minutes'
END AS duration
, [user_name]
, physical_device_name
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D') d --'I for diffs and 'L' for logs
ON a.database_name = d.database_name
AND a.max_backup_finish_date = d.backup_finish_date

Backup Status with AG

SELECT sd.[name]
, sd.state_desc AS database_status
, f.backup_finish_date AS last_full_backup_date
, d.backup_finish_date AS last_diff_backup_date
, CASE WHEN (sd.database_id < 4 OR sd.recovery_model = 3) THEN 'N/A'
ELSE CONVERT (VARCHAR (20), l.backup_finish_date, 120 ) END AS last_log_backup_date
, CASE WHEN (f.backup_finish_date < (DATEADD (DAY, -7, GETDATE())) OR (f.backup_finish_date IS NULL) AND sd.name <> 'TempDB') THEN 'No full backup in the last seven days'
  WHEN (sd.name = 'TempDB') THEN 'N/A'
END AS [missing recent backup]
FROM sys.databases sd
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name ) f
ON sd.[name] = f.database_name
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'I'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
) d
ON sd.[name] = d.database_name
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'L'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
) l
on sd.[name] = l.database_name
INNER JOIN
(SELECT
                        d.[name], hars.role_desc, ag.[name] AS [ag_group_name]--, *
                FROM
                        sys.DATABASES d
                        INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
                        INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id

                        WHERE role_desc = 'PRIMARY'
                        ) ag
ON sd.[name] = ag.[name]

WHERE sd.[name] <> 'tempdb'