Backup status in SQL Server module

[Martin Stevnhoved]

It would be nice with a build in option to trigger notifications based on the date since last backup of any databse.

We are currently using this custom SQL Query i 'msdb':

SELECT STUFF((SELECT N', ' + name FROM (SELECT TOP (100) PERCENT db.name FROM (SELECT name FROM master.sys.databases WHERE (state = 0) AND (NOT (name IN ('tempdb')))) AS db LEFT OUTER JOIN (SELECT database_name AS name, backup_finish_date AS timestamp FROM dbo.backupset UNION SELECT name, create_date FROM master.sys.databases) AS b ON b.name = db.name GROUP BY db.name HAVING (NOT (1 * ISNULL(STR(ABS(DATEDIFF(hh, GETDATE(), MAX(b.timestamp)))), - 1) BETWEEN 0 AND 48)) ORDER BY db.name) AS [n1] FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') AS [Missing]

// Martin.

[Martin Stevnhoved]

Have you looked in to this?

It looks like a very easy quickfix for you, and would help us a lot.

Put two new options in SQL Server config. One for how old a full/differential backup is allowed to be, and one for how old the log backup is allowed to be.

Then loop through the databases (except tempdb)  and check for last backup time for all databases and last log backup for databases not in "simple" recovery mode.

Last full or differential backup (for model db as an example)

SELECT        TOP (1) database_name, backup_finish_date
FROM            msdb.dbo.backupset
WHERE        (type IN ('D', 'I')) AND (database_name = N'model')
ORDER BY backup_finish_date DESC

Last log backup (for model db as an example)

SELECT        TOP (1) database_name, backup_finish_date
FROM            msdb.dbo.backupset
WHERE        (type = 'L') AND (database_name = N'model')
ORDER BY backup_finish_date DESC

You could also look at the databases create time, so we don't get notified because the first backup hasn't run yet.

[Paul]

Hi Martin,

Thanks. I've pushed this into our todo list. It will be picked by the development team and we'll notify you when this is implemented.

-Paul

[Martin Stevnhoved]

Any update @Paul?

Edited December 4, 2018 by Martin Stevnhoved