Using Last Backup Date to Make Sure You get a Full when You need one, and a Differential when you Don't

Today I was glancing at once of my servers and noticed the backup job was running later than normal. I haven’t been working with this server for long, so I glanced to check where the backup was writing to and checked the output directory. I found that a differential backup was being written, and that the differential backup from the day before was much larger than normal.

Having quick backup times on weekdays on this instance and also saving space on the backup server where possible are both priorities, so the backup job for this server was set to do a full backup on Saturday, and a differential backup every other day of the week.

However, there was a production incident on this instance on Saturday, and the backup job was canceled by the person on call. I didn’t receive a monitoring ticket (since it was a cancellation it didn’t throw an alert), and the person on call must not have checked the backup schedule or thought to mention it.

So today I thought about it a bit, and then recalled that there’s a way to check the last backup time. I wanted to use this so I could have the job do a check as to when the last successful full backup time was, and if it was long enough, to do a full backup no matter what. On this particular database, after more than a week the differentials get a bit out of hand and we need to get in a full– ideally the job would be smart enough to realize this on Sunday if the run on Saturday has an issue.

I’ve even written a script that checked last backup time before! But I thought, “Oh, this must be easy, I’ll just check the usual places for the last full backup date.”

And so I did. Since it shows on the database properties in management studio, it seemed like it must be someplace obvious. Here are some places that last backup time is not listed:

• sys.databases
• the databasepropertyfunction
• the databasepropertyex function

Where is it? Well I finally dug it out of my existing script, and my memory. It’s in msdb, in the backupset table. And once I looked at the existing script, I was glad I did. I think I may brush it up and use it pretty soon– it does a check for last successfull backup time across multiple servers. That can be pretty handy to audit, particularly when you’re taking on responsibility for new servers and want to do a quick high level inventory. (Of course, for a quick check you could also write a query to loop through databases on an instance and use the multi-server execution feature in SSMS 10.)

Here’s the query I’m using to find the last full backup, complete with some pseudo-code explaining a bit about how it could be used:

declare
@backup_cmd  nvarchar(4000)
, @daysSinceLastBackup int
, @dbname sysname

set @dbname = {however you want to set this, in a loop or explicitly}

--Figure out how many days since the last backup!
select
      @daysSinceLastBackup=coalesce(datediff(dd,b1.backup_finish_date,getdate()),365)
from msdb..backupset b1 with (nolock)
where
      b1.database_name=@dbName
      and b1.[type] = ('D') -- database backup (not log!)
      and is_damaged=0
      and b1.backup_finish_date=
            (select max(b2.backup_finish_date)
            from msdb..backupset b2 with (nolock)
            where b2.database_name=b1.database_name
            and b2.[type]=b1.[type])

-- Then use the logic to build a differential or full backup command...

IF @DayofWeek IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Sunday')  and @daysSinceLastBackup < 7
BEGIN
{some code....}

END