How to estimate the size of your TFS database backup files


This post is from Senior App Dev Manager, Chev Bryan who shares how to estimate the size of your TFS database backup files.


imageThe 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]

Read the original post on Chev’s blog.


Premier Support for Developers provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality.  Contact your Application Development Manager (ADM) or email us to learn more about what we can do for you.

Comments (0)

Skip to main content