Diff backups older than expected

Comments are closed
How can we help?
< All Topics
Print

Diff backups older than expected

Alert is disabled by default but can be enabled in dbo.alerts by changing enabled from 0 to 1.

Alert will be thrown if the last differential backup is older than expected. The alert can be configured by editing the statement in the sql column in dbo.alerts.

The values for when the alert should trigger can be configured by the following values:

declare @diffBackupIntervalInHours int = 24
declare @maxFullBackupTimeInHours int = 6

@diffBackupIntervalInHours defines how many hours there are between the backups.

@maxFullBackupTimeInHours defines the maximum time it takes for the full backup (not the differential backup) to complete.

To query the Performance Store database for diff backups that are older than expected, use:

declare @diffBackupIntervalInHours int = 24
declare @maxFullBackupTimeInHours int = 6

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.server_name, s1.database_name, s1.last_backup_date
from
(
	select s3.server_name, s3.database_name, max(s3.last_backup_date) last_backup_date, s3.last_failover_date, s3.availability_group_name, s3.create_date
	from
	(
		select s2.server_name, s2.database_name
		, case
			when isnull(s2.last_full_backup_date, 0) > isnull(s2.last_diff_backup_date, 0) then last_full_backup_date
			else s2.last_diff_backup_date
		end last_backup_date
		, s2.last_failover_date, s2.availability_group_name, s2.create_date
		from
		(
			select d.server_name, d.database_name, d.last_diff_backup_date, null last_full_backup_date, d.last_failover_date, d.availability_group_name, d.create_date
			from dbo.v_databases d
			inner join
			(
				select d1.group_database_id, max(isnull(d1.last_diff_backup_date, 0)) last_diff_backup_date, max(isnull(d1.last_failover_date, 0)) last_failover_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_diff_backup_date = d.last_diff_backup_date and d.role_desc = 'PRIMARY'

			union all

			select d.server_name, d.database_name, null last_diff_backup_date, d.last_full_backup_date, d.last_failover_date, d.availability_group_name, d.create_date
			from dbo.v_databases d
			inner join
			(
				select d1.group_database_id, null last_diff_backup_date, max(isnull(d1.last_full_backup_date, 0)) last_full_backup_date, max(isnull(d1.last_failover_date, 0)) last_failover_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'
		) s2
	) s3
	group by s3.server_name, s3.database_name, s3.last_failover_date, s3.availability_group_name, s3.create_date

	union all

	select d.server_name, d.database_name
	, case
		when d.last_full_backup_date > d.last_diff_backup_date then last_full_backup_date
		else d.last_diff_backup_date
	end last_backup_date
	, d.last_failover_date, d.availability_group_name, 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_backup_date < dateadd(hour, -(@diffBackupIntervalInHours + @maxFullBackupTimeInHours), getdate())
and s1.create_date < dateadd(hour, -(@diffBackupIntervalInHours + @maxFullBackupTimeInHours), getdate())
and s2.sample_time > dateadd(hour, -(@diffBackupIntervalInHours + @maxFullBackupTimeInHours), getutcdate())
and s1.last_backup_date > s1.create_date
and (s1.availability_group_name is null or (s1.last_failover_date is null or s1.last_failover_date < dateadd(hour, -(@diffBackupIntervalInHours + @maxFullBackupTimeInHours), getutcdate())))
and s1.database_name not in (@localManagementDatabase, N'tempdb', N'model', N'master', N'distribution', N'msdb', @dummyDatabaseName)
and s2.is_production = 1

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

Table of Contents