Sql-server – Show backups at server level

backupsql serverssms

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)

SELECT serverName
         , MAX(CLastRunDateTime) AS CLastRunDateTime
    FROM  jobs
    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.

Best Answer

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.

To my understanding you want the job to report on the latest full backups on an instance when any of these are not meeting your thresholds.

In that case you could opt to get the the latest FULL backup of all databases, and then get the minimum value of that resultset.

;WITH CTE AS
(
SELECT 
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases 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')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT  @@SERVERNAME as serverName, 
        MIN(LastBackUpTime) as LastBackUpTime
FROM CTE;

If you also need the database name of the database with the oldest successful full backup

;WITH CTE AS
(
SELECT 
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases 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')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT TOP(1)  @@SERVERNAME as serverName, 
        LastBackUpTime as LastBackUpTime,
        DatabaseName
FROM CTE
ORDER BY LastBackUpTime ASC;

I would not use job execution stats, as they can return false positives.

Next to checking if the backup jobs did their job correctly, you should also validate your backups by restoring them somewhere.

Related Question