We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.
SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.
Here is my query. I am only wanting the information from the last 24 hours so have filtered the start date by subtracting 1 day from today. I have also provided some commented out options in case someone needs them.
— database backup size and how long it took to do backup
SELECT bs.database_name AS DatabaseName
, CAST(bs.backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS BackupSizeGB
, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB
–, CAST(bs.compressed_backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS CompressedSizeGB
–, CAST(bs.compressed_backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS CompressedSizeMB
, bs.backup_start_date AS BackupStartDate
, bs.backup_finish_date AS BackupEndDate
, CAST(bs.backup_finish_date – bs.backup_start_date AS TIME) AS AmtTimeToBkup
, bmf.physical_device_name AS BackupDeviceName
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
–bs.database_name = ‘MyDatabase’ and — uncomment to filter by database name
bs.backup_start_date > DATEADD(dd, -1, GETDATE()) and
bs.type = ‘D’ — change to L for transaction logs
ORDER BY bs.database_name, bs.backup_start_date
And, here is the output.
It turned out that the software was indeed compressing all backups so that was a good thing.
There is a lot more info that can be pulled from msdb regarding backups. Have some fun and experiment with getting information that you need from there. Here are some links to some other backup related topics that we have blogged about already.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
[…] Lori Brown shows how to query the msdb database and get information on backups, including whether th…: […]