Jeremy Carter

Database Backup Disk Space Capacity Planning. Try saying that three times fast!

Thomas LaRock (aka SQLRockstar) just posted an excellent piece on capacity planning. As I was reading this I was thinking about how true it is that for so many people we just do not spend adequate time planning and there are not alot of resources out there to refer to.  At the same time I had just recently worked with a client planning their recovery strategy when the topic of disk space for the backups came up. Basically we needed to figure out how much disk space they would need for their objectives so that the proper sized san luns could be carved out. I had previously written this script to fullfill that need and thought I would share with everyone. I find it very handy for looking for trends in backup sizes as well as planning disk space/retention requirements.

The script is pulling alot of information from the backup history tables in msdb and then formatting it into a nice report. It will display how much the full, diff, and log backups are taking on a daily basis for each database along with totals. I’ve included a small sample of a result set below (with the db name changed to protect the innocent). In the example you can see the results for the summary of All Databases along with one of the databases.

SELECT
	[Database Name], [Start Date], [All Types],
	ISNULL([Full],'') AS [Full],
	ISNULL([Differential],'') AS [Differential],
	ISNULL([Transaction Log],'') AS [Transaction Log]
FROM(
	SELECT
		ISNULL(bu.database_name,'*All Databases')AS [Database Name],
		CASE bu.type
			WHEN 'D' THEN 'Full'
			WHEN 'I' THEN 'Differential'
			WHEN 'L' THEN 'Transaction Log'
			ELSE 'All Types'
		END as [Backup Type],
		ISNULL(convert(char(10), backup_start_date, 120),'All Dates' )as [Start Date],
		CAST((SUM(bu.backup_size/1024/1024)) AS int) AS [Size in MB]
	FROM msdb.dbo.backupset as bu
	WHERE DATEDIFF(d, backup_start_date, GETDATE()) <= 6
	GROUP BY bu.database_name, convert(char(10), backup_start_date, 120),  bu.type
	WITH CUBE
) AS SourceTable
PIVOT(SUM([Size in MB]) FOR [Backup Type] IN ([All Types], [Full], [Differential], [Transaction Log])) AS PivotTable
ORDER BY [Database Name] ASC, convert(char(10), [Start Date], 120) DESC