Martin Stevnhoved Posted May 27, 2016 Share Posted May 27, 2016 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. Tyson and Paul 2 Link to comment Share on other sites More sharing options...
Martin Stevnhoved Posted February 10, 2017 Author Share Posted February 10, 2017 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 1 Link to comment Share on other sites More sharing options...
Administrators Paul Posted February 13, 2017 Administrators Share Posted February 13, 2017 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 Tyson and Martin Stevnhoved 2 Link to comment Share on other sites More sharing options...
Martin Stevnhoved Posted November 29, 2018 Author Share Posted November 29, 2018 (edited) Any update @Paul? Edited December 4, 2018 by Martin Stevnhoved Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now