When was your last database backup?

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.

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

One thought on “When was your last database backup?

  1. yimesgen

    Thanks for the script!
    How about if there is no preferred replica? the backup can happen in one of the active replicas and it would be nice if your query can show up the latest backup taken on one of the replicas.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *