Share via

How to estimate the size of your Tfs database backup files

The most likely way to do this in my experience is to take a look at the most recent backup job. This can be done quite easily from a T-SQL script. Feel free to refine the bellow to suite your needs, but it should get you the basic information you require.

 SELECT TOP 100 s.database_name AS [Database Name], m.physical_device_name AS [Backup File Physical Location], CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))
 + ' ' + ' MB' AS [Size of Backup File (MB)], s.backup_start_date AS [Start Time], s.backup_finish_date AS [End Time], CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) AS [Backup Duration (Seconds)], s.server_name AS [SQL Server Name], CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS [Type of Backup],
 s.recovery_model AS [Recovery Model]
 FROM msdb.dbo.backupset AS s INNER JOIN
 msdb.dbo.backupmediafamily AS m ON s.media_set_id = m.media_set_id
 WHERE (s.database_name LIKE 'tfs_%')
 ORDER BY [Start Time] DESC, [End Time]


A special thank you to SQL Authority for the base script, with a couple tweaks, I was able to get the exact results I wanted.