Backup History and Information

How long a database takes to backup is something that over the years I have been asked to get.  These requests come for different reasons, sometimes it could be to find out how much it has increased over time, sometimes it could be to see if the backup job is interfering with other jobs and sometime it isn’t about duration at all, it is more about showing the backups were completed.  Over the years I have had several auditors ask for backup history.

In order to get this information, we need to pull data from two tables in the MSDB database, backupset and backupmediafamily.


Below is how Microsoft describes that Backupset table.  This table is in the MSDB database.

“Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation.”

Since most audits usually are for a date range, the start date of the backup is how we will limit the rows returned to only the ones we need.

This table also has a column named Type.  This is where you can identify the type of backup.  Usually audits, at least the ones I have been involved in, usually only care about Full and Differential backups.  Because of this we will use this column to limit the rows returned to just that two back up types.  You can adjust as needed for your environment.

According to Microsoft’s documentation, these are the backup types you might find in this table.  This can be found here.



This table has many columns, however for our purposes we will only use one, physical_device_name.  This column will store the destination of the backup.

I like to include this column because if we need to do a restore, this will help us find the proper back up.  In addition, it could also help us determine if a third part tool is being used to back up the database.

In the physical_device_name column you will usually see something like this:

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\100MBDB_backup_2020_02_01_061030_4137875.trn

As you can see the above path not only identifies the destination folder, it also has the file name.

Sometimes, you will see something like below:


Most of the time, although not all, this is because the database if being backed up by a third-party tool such as COMMVAULT.

Here is the code that pulls the data from the two tables mentioned above. If you look at the join, you will see that the two tables are joined on the media_set_id column in both tables.

SELECT s.database_name
, m.physical_device_name ‘DestinationLocation’
, CAST(DATEDIFF(SECOND, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4))
+ ‘ ‘ + ‘Seconds’ AS ‘Duration’
, s.backup_start_date
, CASE WHEN ROW_NUMBER() OVER(PARTITION BY s.database_name ORDER BY s.backup_start_date ASC) = 1
DATEDIFF(DAY,LAG(s.backup_start_date, 1,0)
OVER(ORDER BY DAY(s.backup_start_date)), s.backup_start_date)
END ‘DaysSinceLastBackup’
, CASE s.[type]
WHEN ‘D’ THEN ‘Full’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Transaction Log’
END ‘BackupType’
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.backup_start_date >= ’04/01/2020′
AND s.backup_start_date <= ’06/30/2020′
AND s.type IN (‘D’, ‘I’)
ORDER BY s.database_name, s.backup_start_date

The above code will return a result set similar to below.  Notice the results include, Database Name, Destination, duration, start time, Days since last backup and Backup Type.

This is much of the information that I have had to gather for various reasons over the years.

Notice the column DaysSinceLastBackup.  This uses the LAG function to calculate the number of days between the current row back up start time and the previous row start time. This can be used to help identify if there are any missed backups.  We have used this as the foundation of an alert that will email us if a backup is missing.

Over the years I have found this code to be very useful.  While this focuses mostly on full and differential backups, it can easily be modified to return other backups times as well.  However, keep in mind the impact that might have on the DaysSinceLastBackup.  If you also return transaction log backups, this number might not be as useful as you might like it to be.

Unlike a DMV, the data in these tables will persist after a reboot.

Thanks for visiting my blog!!!