How Do You Determine How Much Disk Space You Need If You’re Handed 18 SQL Server Backups and No History?

Happy last day of June, dear reader.

Today’s post comes from one of those real-world DBA situations where someone hands you a folder full of SQL Server backup files and asks what should be a simple question:

“How much disk space do we need to restore these?”

Simple question.

Not always a simple answer.

Let’s say you are handed 18 SQL Server database backups.

You do not have backup history.
You do not know the original database sizes.
You do not know if the backups are compressed.
You do not know how large the data and log files will be once restored.

All you have are the backup files.

So what do you do?

You do what DBAs do best.

You inspect the evidence.

Problem

A SQL Server backup file does not always tell the full story when viewed by file size alone.

A 50 GB backup file does not necessarily mean the restored database will consume 50 GB of disk space.

Depending on backup compression, database file layout, log file size, and unused allocated space inside the database files, the restored database could be much larger than the backup file sitting on disk.

This becomes especially important when you are restoring multiple databases and need to request or provision storage before the restore begins.

Guessing is risky.

Running out of disk space halfway through a restore is worse.

Goal

The goal is to determine how much disk space is required before restoring the databases.

To do that, we want to:

Inspect each backup file
Determine the data and log file sizes inside the backup
Total the estimated restore size
Add room for growth
Add a safety buffer
Document the results

Why Backup File Size Is Not Enough

The backup file size and the restored database size are not the same thing.

A backup may be compressed.
The database may contain multiple data files.
The transaction log may be much larger than expected.
There may be allocated but unused space inside the database files.

The only reliable way to estimate restore size is to read the file list from the backup.

Step 1: Use RESTORE FILELISTONLY

SQL Server gives us exactly what we need with RESTORE FILELISTONLY.

This command reads the backup file and returns information about the files contained inside the backup.

RESTORE FILELISTONLY

FROM DISK = 'D:\Backups\YourDatabase.bak';

This does not restore the database.

It simply reads the backup metadata.

The most important columns for this exercise are:

LogicalName
PhysicalName
Type
FileGroupName
Size
MaxSize

The Type column tells you whether the file is a data file or log file.

D = Data
L = Log

The Size column tells you the allocated file size.

This is the number we care about.

Step 2: Understand the Size Column

One important detail: the Size column is reported in bytes.

That means we need to convert it into MB or GB to make it useful.

Example conversion:

SELECT

SizeMB = Size / 1024.0 / 1024.0,

SizeGB = Size / 1024.0 / 1024.0 / 1024.0;

If you are looking at the output manually, this helps you understand what the restore will need from a disk perspective.

Step 3: Repeat for All Backup Files

If you only have one backup file, this is easy.

If you have 18 backup files, manually running RESTORE FILELISTONLY against each file gets old quickly.

This is where PowerShell becomes useful.

PowerShell Example

The following script loops through backup files in a folder, runs RESTORE FILELISTONLY, captures the file sizes, and summarizes the results.

$SqlInstance = "localhost"
$BackupPath  = "D:\Backups"

$Results = foreach ($BackupFile in Get-ChildItem -Path $BackupPath -Filter "*.bak") {

Write-Host "Processing $($BackupFile.FullName)" -ForegroundColor Cyan

$Query = "RESTORE FILELISTONLY FROM DISK = N'$($BackupFile.FullName)'"

$FileList = Invoke-Sqlcmd `
        -ServerInstance $SqlInstance `
        -Query $Query

foreach ($File in $FileList) {
        [PSCustomObject]@{
            BackupFile   = $BackupFile.Name
            LogicalName  = $File.LogicalName
            Type         = $File.Type
            PhysicalName = $File.PhysicalName
            SizeMB       = [math]::Round($File.Size / 1024 / 1024, 2)
            SizeGB       = [math]::Round($File.Size / 1024 / 1024 / 1024, 2)
        }
    }
}

$Results | Format-Table -AutoSize

$Summary = $Results |
    Group-Object BackupFile |
    ForEach-Object {
        [PSCustomObject]@{
            BackupFile = $_.Name
            TotalSizeGB = [math]::Round(($_.Group | Measure-Object SizeGB -Sum).Sum, 2)
        }
    }

$Summary | Format-Table -AutoSize

$TotalRestoreSizeGB = [math]::Round(($Summary | Measure-Object TotalSizeGB -Sum).Sum, 2)

Write-Host ""
Write-Host "Estimated restore size for all backups: $TotalRestoreSizeGB GB" -ForegroundColor Green

$SqlInstance = "localhost"

$BackupPath = "D:\Backups"

$Results = foreach ($BackupFile in Get-ChildItem -Path $BackupPath -Filter "*.bak") {

Write-Host "Processing $($BackupFile.FullName)" -ForegroundColor Cyan

$Query = "RESTORE FILELISTONLY FROM DISK = N'$($BackupFile.FullName)'"

$FileList = Invoke-Sqlcmd `

-ServerInstance $SqlInstance `

-Query $Query

foreach ($File in $FileList) {

[PSCustomObject]@{

BackupFile = $BackupFile.Name

LogicalName = $File.LogicalName

Type = $File.Type

PhysicalName = $File.PhysicalName

SizeMB = [math]::Round($File.Size / 1024 / 1024, 2)

SizeGB = [math]::Round($File.Size / 1024 / 1024 / 1024, 2)

}

$Results | Format-Table -AutoSize

$Summary = $Results |

Group-Object BackupFile |

ForEach-Object {

[PSCustomObject]@{

BackupFile = $_.Name

TotalSizeGB = [math]::Round(($_.Group | Measure-Object SizeGB -Sum).Sum, 2)

}

$Summary | Format-Table -AutoSize

$TotalRestoreSizeGB = [math]::Round(($Summary | Measure-Object TotalSizeGB -Sum).Sum, 2)

Write-Host ""

Write-Host "Estimated restore size for all backups: $TotalRestoreSizeGB GB" -ForegroundColor Green

Step 4: Export the Results

I like to export the results to CSV so there is a record of how the estimate was built.

$Results | Export-Csv "D:\Backups\BackupFileDetails.csv" -NoTypeInformation

$Summary | Export-Csv "D:\Backups\BackupRestoreSummary.csv" -NoTypeInformation

This gives you something useful to keep with the restore request, storage ticket, or migration documentation.

Step 5: Add a Safety Buffer

Once you know the estimated restore size, do not request exactly that amount of storage.

That is asking for trouble.

At a minimum, I usually like to add 20-30% depending on the situation.

Example:

Estimated restored database size: 500 GB

Growth buffer at 30%: 150 GB

Safety buffer at 20%: 130 GB

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

Estimated disk needed: 780 GB

This is not a perfect formula, but it is a much better starting point than guessing.

Things to Consider

Before finalizing the disk request, consider the following:

Autogrowth

If the databases are expected to grow immediately after restore, account for that growth.

Log Files

Log files may be larger than expected. Do not ignore them.

Future Work

Will you run CHECKDB after the restore?
Will indexes be rebuilt?
Will data be modified immediately after restore?

All of these can influence how much working space you need.

Example Capacity Estimate

Here is a simple way to present the estimate:

Total data file size from backups: 400 GB

Total log file size from backups: 100 GB

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

Estimated restore size: 500 GB

Expected growth buffer 30%: 150 GB

Safety buffer 20%: 130 GB

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

Recommended disk allocation: 780 GB

This gives you a clean explanation if someone asks why you requested more storage than the backup files currently consume.

Lessons Learned

When you are handed SQL Server backups with no history, do not rely on the backup file size alone.

Use the metadata inside the backup.
RESTORE FILELISTONLY is your friend.
PowerShell can help automate the boring part.
Add room for growth.
Add a safety buffer.
Document the results.

Your future self, your storage team, and the next DBA will thank you.

Final Thoughts

This is one of those tasks that seems simple until you are the person responsible for making sure the restore succeeds.

A little preparation goes a long way.

Plan today.
Restore tomorrow.
Sleep well.

Mystery Backups: Estimating Disk Space When All You Have Are .BAK Files5 min read