I have an SSRS report which shows the 'last backup time' next to each server in the list. I am getting this information by using:
SELECT TOP 1 @@SERVERNAME as serverName,
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
GROUP BY sdb.Name
ORDER BY LastBackUpTime DESC
The problem here is that if for example 7 out of 8 database backups fail in a job, that job is flagged up as 'failed', but the report will show that the last backup time was in that batch of failed backups, which it was, as it is selecting the one backup of 8 which succeeded.
Prior to this I was finding the last run time of the specific job from a table which I inserted the sysjobs and sysjobservers information into, as most of our backup jobs are hallengren jobs named identically. (see below)
, MAX(CLastRunDateTime) AS CLastRunDateTime
WHERE name = 'DatabaseBackup - USER_DATABASES - FULL'
The problem with this is that there are some servers which have a non hallengren named job, so it involves 'fiddling' to insert the correct job name. There are also some servers which do not have agent and have backups taken manually, so job name cannot be used to pick up the last backup time.
How can I get a backup time at server level with as little 'fiddling' as possible. I understand the problems, as some hallengren jobs exclude certain databases, so it cant be based on 'when were all backups taken on this server' - it has to be on the one hand job specific, on the other hand not job specific. Maybe something like, when the job name is 'X', 'Y', 'Z', then find it this way, when job name DOES NOT EXIST, then use max backup for last database? But that seems to involve the fiddling around that I don't want.
I'm not sure. Wondering if anyone has a good way of doing this.