How to Forecast the Size of Restore of Backup in SQL Server? – Interview Question of the Week #265

Question: How do you estimate the disk space needed to restore a SQL Server backup? Read the file sizes with RESTORE FILELISTONLY. The size of the .bak file alone doesn’t answer the question.

A DBA brought this up during a Comprehensive Database Performance Health Check. We had a large backup and limited free disk space on the destination. Before starting the restore, we needed to know how large its data and log files would be.

RESTORE FILELISTONLY
FROM DISK = N'H:\data\adw.bak';

Replace the path with your backup location. If the media contains several backup sets, select the intended set with WITH FILE = n. This command reads metadata; it doesn’t restore the database.

The sample data file is 1,216,348,160 bytes and the log file is 679,477,248 bytes. Together they need 1,895,825,408 bytes, approximately 1.896 GB or 1.766 GiB, before allowing for anything else.

SELECT file_type, size_bytes,
       CAST(size_bytes / 1000000000.0 AS decimal(10,3)) AS size_GB,
       CAST(size_bytes / 1073741824.0 AS decimal(10,3)) AS size_GiB
FROM (VALUES ('Data', CAST(1216348160 AS bigint)),
             ('Log',  CAST(679477248 AS bigint))) AS s(file_type, size_bytes);

SELECT CAST((CAST(1216348160 AS bigint) + 679477248)
            / 1000000000.0 AS decimal(10,3)) AS total_GB;

GB uses 1,000,000,000 bytes; GiB uses 1,073,741,824. Mixing those divisors makes an already tight disk-space calculation confusing.

Check each destination volume

If the data and log will go to different volumes, check each volume separately. Include every file you plan to restore. Compression can make the backup much smaller than the allocated files, and BackupSizeInBytes is not the same measurement as Size.

The roughly 2 GB in this example is an allocation estimate, not a comfortable operating budget. Leave room for growth and other activity. A restore sequence that applies later differential or log backups can also need more space if files grew after the full backup.

The answer is pleasantly simple: inspect the file list before you start. A small backup file can still require a large destination.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.