Estimating the Size of your Database Backups

I think I have answered a dozen or so questions regarding how to estimate the size of database backups without running the backup in the last few months on the Forums.  It is really quite simple to estimate how large a backup will be for a database.  The sp_spaceused system stored procedure will show how much reserved space there is in the database.  This is roughly equivalent to the size that the backup will be when it completes.

USE SQLCLR_Examples
GO
EXEC sp_spaceused @updateusage = 'true'
           

database_name                         database_size      unallocated space

------------------------------------- ------------------ ------------------

SQLCLR_Examples                       6031.50 MB         4326.55 MB

reserved           data               index_size         unused

------------------ ------------------ ------------------ ------------------

988624 KB          944216 KB          43200 KB           1208 KB

I create a backup with the following command:

BACKUP DATABASE [SQLCLR_Examples]
TO DISK = N'D:\SQLCLR_Examples.bak'
WITH NOFORMAT, NOINIT,
NAME = N'SQLCLR_Examples-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10

Then look at its size with the following command:

SELECT CONVERT(VARCHAR, CONVERT(DECIMAL(18,1), backup_size/1024))+ ' KB' [Backup Size]
FROM msdb.dbo.backupset
WHERE database_name = 'SQLCLR_Examples'
  AND backup_finish_date > DATEADD(hh, -1, GETDATE())



Backup Size

---------------------------------

993573.5 KB

So you can see from this demonstration that the size of the backup is roughly equal to the reserved space in the database from sp_spaceused.  The updateusage parameter is sometimes needed to account for changes that have occured but are not yet reflected in the usage stats for the database.