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 a number of auditors ask of backup history.
In order to get this information we need to pull data from two tables in the MSDB database, backupset and backupmediafamily.
Read on to learn about these two tables and to get a sample query. On systems with a large number of databases and a DBA who loves frequent transaction log backups (like I do), this table can get pretty big, so don’t forget to prune that data over time.