Databases not backed up

To query the Performance Store database for databases that are not backed up, use:

declare @localManagementDatabase nvarchar(128)

select @localManagementDatabase = c.value
from dbo.config c
where c.name = 'LocalManagementDatabase'

declare @dummyDatabaseName nvarchar(128)

select @dummyDatabaseName = c.value
from dbo.config c
where c.name = 'DummyDatabaseName'

select s1.*
from
(
	select d.server_name, d.database_name, d.last_full_backup_date, d.create_date
	from dbo.v_databases d
	inner join
	(
		select d1.group_database_id, max(d1.last_full_backup_date) last_full_backup_date
		from dbo.v_databases d1
		where d1.group_database_id is not null
		group by d1.group_database_id
	) s on s.group_database_id = d.group_database_id and s.last_full_backup_date = d.last_full_backup_date and d.role_desc = 'PRIMARY'

	union all

	select d.server_name, d.database_name, d.last_full_backup_date, d.create_date
	from dbo.v_databases d
	where d.group_database_id is null
	and d.state_desc = 'ONLINE'
) s1
inner join dbo.v_servers s2 on s2.server_name = s1.server_name
where s1.last_full_backup_date is null
and s1.create_date < dateadd(week, -1, getdate())
and s1.database_name not in (@localManagementDatabase, N'tempdb', @dummyDatabaseName)
and s2.is_production = 1

The alert will be visible on the Latest stats dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.