Estimating SQL Server backup sizes — with and without compression

How big will my backup file be?  Very good question.  And of course, it depends... especially if you're using compression.

I was working something today that required a backup beforehand for safety measures, and the server had limited storage.  So, I needed to estimate the backup file size beforehand to confirm whether I had enough space for the bak file.

This script will show you how to estimate the backup size, both uncompressed and compressed, using a 30% reduction, which is a realistic compression ratio.  Please understand, the actual backup sizes vary and depend on table contents, data types, how much empty space exists in the data files and more, but 30% is a safe rule-of-thumb that works well for general estimates.

USE master;

-- input your database name here

DECLARE @dbname SYSNAME = 'DBA';

-- estimate compressed size using 30% compression ratio

DECLARE @compratio DECIMAL(5,2) = 0.30;

-- estimate backup size

SELECT 

    DB.name [Database],

    CAST(SUM(MF.size) * 8.0 / 1024 AS DECIMAL(18,2)) [CurrentDB_SizeMB],

    CAST(SUM(MF.size) * 8.0 / 1024 AS DECIMAL(18,2)) [Estimated_BackupSizeMB_Uncompressed],

    CAST(SUM(MF.size) * 8.0 / 1024 * (1 - @compratio) AS DECIMAL(18,2)) [Estimated_BackupSizeMB_Compressed],

    @compratio [Assumed_Compression_Ratio]

FROM 

    sys.master_files mf INNER JOIN sys.databases db 

  ON mf.database_id = db.database_id

WHERE 

    DB.name = @dbname

    AND MF.type_desc IN ('ROWS', 'LOG')

GROUP BY 

    DB.name;

 

Example output:

 

Of course, you can also use the backup history to estimate backup size, but the above is just a quick method to estimate the backup size, compressed or not, using just the current database size and the compression ratio -- in this case, 30%.