Jump to content

Backup status in SQL Server module


Recommended Posts

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.

Link to comment
Share on other sites

  • 8 months later...

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.

Link to comment
Share on other sites

  • 1 year later...

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...