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 old Backup Set report is obsolete and has been replaced with the newer Backup Report in the Database Health Reports project.






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
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,

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

2 Responses to Backup Set

  1. Pingback: » Recursive CTE’s Steve Stedman

  2. Pingback: » Backup Set Report added to FREE Server Health Reports Steve Stedman

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by sweet Captcha