1

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.

  • What type of backups: Full, Full Copy-Only, Differential, TLog, etc.? – John Eisbrener Jun 12 at 13:16
  • 1
    It sounds like you are trying to get an answer to "When was the server last backed up?" But backups don't work that way. Backups are a database-by-database concept. For a given database, a backup will have succeeded or failed, so you can report on a "last backup time". But for an entire server, you don't have a concept of a "last backup time". Let's re-think the question -- what are you trying to accomplish? – Doug Deden Jun 12 at 14:26
0

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.

  • Thank you very much, I edited this to look for the minimum value in the last 30 days else show null, and I am now much closer to a solution. – jack Jun 13 at 9:03
  • @jack great! Thanks for giving an update :). Good luck! – Randi Vertongen Jun 13 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.