Backup Set

If you have arrived at this page as a result of running the Server Health report, congratulations you are on the first step to improving your server health.

The backupset table in the SQL Server database contains a history of all backups that have been run since the last time that the backupset table was purged.

You can view sample output from the report by downloading the BackupSetReport.pdf.

What does this mean to my database?

What this means really depends on what the report looks like.  If you have a database with a backup file that is growing faster than you would expect then there are many things that you can check.

Recently I used this report to find a database that had a backup file that had grown to 90gb, then after doing some research I was able to do some reorganizing of indexes and trim 20% off the backup file without removing any data.

How it is done


WITH largest10databasescte(database_name) AS (
SELECT TOP 10 database_name FROM (
SELECT database_name, MAX(compressed_backup_size) + MAX(backup_size) AS size
FROM msdb.dbo.backupset
WHERE TYPE = 'D'
GROUP BY database_name ) AS t
ORDER BY t.size DESC )

SELECT bs.database_name,
bs.backup_size / 1024 / 1024 AS backup_size,
bs.compressed_backup_size / 1024 / 1024  AS compressed_backup_size,
bs.backup_finish_date

FROM msdb.dbo.backupset bs
INNER JOIN largest10databasescte l10 ON l10.database_name = bs.database_name
WHERE backup_finish_date > Getdate()- 90
AND bs.TYPE = ‘D’
ORDER BY database_name ASC, backup_finish_date DESC