I'd be happy with that, but how do you guarantee that you get the email? That will be the one time that the SMTP is down or whatever ...
I've been toying with the idea of getting the server to send some data every hour to a central server. I have no idea, yet, how it would "send" that data ... all ideas welcome!
I might be able to PULL the data - we have permissions etc. to connect to all the Client servers, not sure I want to use Linked Servers, but i could have a BATch file that used SQLCMD or similar to query each server in turn.
The Absence of any data would be deemed to be a failure ...
This is the code that I have got so far (to check for missing backups). I reckon I also need a script for Failed SQL Agent jobs
-- Missing backups - List Databases with no recent backup
DECLARE @dtFull datetime = DateAdd(Day, -8, GetDate())
, @dtDiff datetime = DateAdd(Day, -2, GetDate())
, @dtTLog datetime = DateAdd(Hour, -2, GetDate()) -- Skip if SIMPLE Recovery Model
FROM master.sys.databases AS D
LEFT OUTER JOIN
, [FullDt] = NullIf(MAX(CASE WHEN type = 'D' THEN backup_finish_date ELSE '19000101' END), '19000101')
, [DiffDt] = NullIf(MAX(CASE WHEN type = 'I' THEN backup_finish_date ELSE '19000101' END), '19000101')
, [TLogDt] = NullIf(MAX(CASE WHEN type = 'L' THEN backup_finish_date ELSE '19000101' END), '19000101')
-- BS.type = 'F' for File Group backups
, [OtherTypes] = SUM(CASE WHEN type IN ('D', 'I', 'L') THEN 0 ELSE 1 END)
FROM msdb.dbo.backupset AS BS
WHERE backup_finish_date <= GetDate() -- Do not include any future-date rows
GROUP BY database_name
) AS BS
ON BS.database_name = D.name
SELECT [T_ErrMsg] = CASE WHEN FullDt > @dtFull THEN '' ELSE 'Stale FULL backup. ' END
+ CASE WHEN DiffDt > @dtDiff THEN '' ELSE 'Stale DIFF backup. ' END
+ CASE WHEN TLogDt > @dtTLog OR D.recovery_model_desc IN ('SIMPLE') THEN '' ELSE 'Stale LOG backup. ' END
+ CASE WHEN OtherTypes = 0 THEN '' ELSE 'Invalid TYPE. ' END
) AS X
AND D.name NOT LIKE 'RESTORE[_]%' -- Ignore any dataasbe named "RESTORE_xxx"
AND D.name NOT IN ('tempdb')
-- AND D.state = 0 -- Only if database is online
AND NullIf(T_ErrMsg, '') IS NOT NULL -- Only list Databases with errors
CASE WHEN T_ErrMsg = '' THEN 2 ELSE 1 END,