When was your last database backup?

Monitoring, SQL

Its pretty often that you have to sit down at a SQL Server and need sort out what the backup situation is. One of the first things that I check is, when did the last backup for each database complete? But answering that question is getting more complicated. If you’re using Availability Groups, you could be offloading your backups to a secondary and that can skew your backup data.  In Availability Groups, database backup history is only stored on the instance that the backup executed on.

So I pulled together a quick script that gave me the following information:

  • Database name
  • Recovery model
  • If the database is in an Availability Group
  • Availability Group Preferred Backup Replica status
  • Date and time of the last full backup
  • Date and time of the last log backup
  • Date and time of the last differential backup
  • Days since the last full backup
  • Days since the last differential backup
  • Minutes since the last transaction log backup

I chose to aggregate the data on the backup type…full, differential and log and when did the last backup of each type complete.

With this data, I can do a quick scan across a row I can determine the recovery model, if the database is in an AG or not, if this is the preferred backup replica and if the appropriate backup type is happening at a regular interval. Also, since I’m efficient (*cough* lazy) I added a quick calculation to show how long in it’s been since a full or differential has completed in days and for log backups how long in minutes.

SELECT  db.name [Name]
      , db.recovery_model_desc
      , CASE WHEN agdb.database_id IS NOT NULL THEN 'AG Database'
             ELSE 'Not AG Database'
        END [IsAgDb]
      , CASE WHEN sys.fn_hadr_backup_is_preferred_replica(db.name) = 1
             THEN 'YES'
             ELSE 'NO'
        END [IsPreferredReplica]
      , t.LastFullBackup
      , t.LastDiffBackup
      , t.LastLogBackup
      , t.DaysSinceLastFullBackup
      , t.DaysSinceLastDiffBackup
      , t.MinutesSinceLastLogBackup
FROM    sys.databases db
        LEFT OUTER JOIN ( SELECT    p.database_name
                                  , MAX(p.[D]) [LastFullBackup]
                                  , MAX(p.[I]) [LastDiffBackup]
                                  , MAX(p.[L]) [LastLogBackup]
                                  , DATEDIFF(DAY, MAX(p.[D]), GETDATE()) [DaysSinceLastFullBackup]
                                  , DATEDIFF(DAY, MAX(p.[I]), GETDATE()) [DaysSinceLastDiffBackup]
                                  , DATEDIFF(MINUTE, MAX(p.[L]), GETDATE()) [MinutesSinceLastLogBackup]
                          FROM      msdb.dbo.backupset bs PIVOT ( MAX(bs.backup_finish_date) FOR [type] IN ( [D], [L], [I] ) )  AS p
                          GROUP BY  p.database_name
                        ) t ON db.name = t.database_name
        LEFT OUTER JOIN sys.dm_hadr_database_replica_states agdb ON agdb.database_id = db.database_id
                                                              AND agdb.is_local = 1;

Here’s some sample output from the script, I chose to leave the NULLs in there to really stand out. In this output you can see that the transaction logs on the AG databases haven’t been backed up in a long time on this instance. We should keep looking to find out why :)

last database backup sql server

Hopefully this script will help you quickly determine what your backups situation is!
Twitter @nocentino