MS SQL Server Backup File Size Requirement

In SQLServer 2012, using below backup command, I started taking a backup and  it first created a 130G BAK file. I was expecting around 60G file and my disk space was around 150G. Because of that I started monitoring, the only information I have was it will be a compressed file, and the current percentage of completion.  Fortunately, it finished with 60G file when it was 100 percent.



:Setvar DB MYDB
:Setvar date 20190102

BACKUP DATABASE [$(DB)] TO DISK = N'C:\SQLBCK\$(DB)_prod_$(date).bak' WITH NOFORMAT, INIT, NAME = N'$(DB) prod-Full Database Backup $(date)', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

In the support note it says creating a file with an estimated file size is for performance benefit. "This size is calculated directly from the number of allocated extents in the data files that are specified for the backup. " Not surprisingly, instead of incrementally allocating disk space, it first creates a target estimate size file, and fills it, and releases unused space at the end.

https://support.microsoft.com/en-us/help/2001026/inf-space-requirements-for-backup-devices-in-sql-server

So, for less space environments, is there a way of opting out performance?

The answer is yes. Not to allocate a big target space, you may enable trace flag 3042 for incremental sizing. For details please look into below article. https://www.mssqltips.com/sqlservertip/2531/sql-server-compressed-backups-disk-space-needs/

Lessons learnt from this experience is just be cautious about the disk space in bigger size databases. It will not be greater than the database.