Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

Backing up the backup folder: SQL .bak files that double the server backup

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A server backup, whether image-level or file-level, copies everything on the volumes it covers. If SQL Server writes its own .bak files to a folder on one of those volumes, the server backup stores those files as well, on top of the database files they came from. The same data is protected twice, and each night's new .bak file is new data to the server backup.

Which copy to keep in the main job depends on where you want the database restore to start from. The cost of doing nothing is easy to measure first.

Why does the server backup store the dump again?

SQL Server's BACKUP DATABASE writes to a "backup file", which Microsoft's documentation calls "a regular operating system file". By default it goes to a disk path the instance can reach: a local drive, a network share, or Azure Blob Storage. To the server backup it looks like any other file in a folder.

The same data now exists in up to three forms in the server backup: the live database files (read through a snapshot or the disk), this week's .bak files, and the earlier .bak files a retention setting keeps on the local disk.

Two details raise the cost:

  • A new file every night. Microsoft notes that SQL Server's management tools "automatically generate a time-stamped name on the disk file", so a typical nightly job produces a new file, not a changed one, and the server backup sees new data.
  • Compressed dumps defeat matching. SQL Server backup compression gives a file whose bytes depend on the whole database state. The linked article on compressed and encrypted files explains why a deduplicating backup store tends to find little in common between two nights of such files. That is an expectation, not a measurement, so test it with your tool.

The .bak file is a good thing to have. SQL Server writes it in a form SQL Server can verify and restore itself. The question is only whether the server backup also needs to hold it.

What are the options?

Option What it does Cost and trade-off
Leave it as it is Both copies stored Highest storage; simplest to restore either way
Put the folder on a separate disk and leave that disk out of the server backup Server backup holds the database files and system; the .bak files live only on that disk The .bak files then exist in one place only. Microsoft advises that a backup disk "be a different disk than the database data and log disks" so backups survive a data disk failure, but a disk on the same server is still lost with the server
Write .bak straight to a share or to Azure Blob Storage Dump goes off the server Microsoft documents both: a UNC path with the SQL Server service account needing access, and BACKUP ... TO URL. Network errors are possible; Microsoft recommends verifying the backup afterwards
Shorten local retention of .bak files Fewer files on disk and in each server backup Does not shrink backup points already taken; those age out under the server backup's own retention
Exclude the folder from the server backup Server backup skips it Needs another off-server copy of the dumps. How to set an exclusion is specific to the backup product, so use its own documentation or ask the provider
Keep the dumps in the server backup and stop dumping Database files only Gives up SQL-native restore, checksum and VERIFYONLY paths (see the linked proof article)

What does it cost?

Example (illustrative): server db01 has 400 GB of database files that change 1% (4 GB) a day. A nightly compressed full .bak is written to D:\SQLBackups and local retention keeps 7 files. The server backup covers all volumes and keeps 30 daily points. All figures are decimal GB and ignore any compression the backup store applies.

Microsoft shows a compression ratio of 3:1 as a saving of about 66% on disk, so each .bak is about 400 / 3 = 133 GB, and 7 files occupy about 7 x 133 = 931 GB on the server.

Assume the server backup stores each distinct night's .bak once and that two compressed .bak files from different nights share almost no bytes.

  • Database files in the server backup: 400 GB first, then 4 GB new on each of 29 later points: 400 + (29 x 4) = 516 GB.
  • .bak folder, first point: 7 files x 133 GB = 931 GB.
  • .bak folder, later points: one new file a night for 29 nights = 29 x 133 = 3,857 GB. The oldest local file is removed each night, but removing it from disk does not remove it from earlier backup points.
  • Folder total: 931 + 3,857 = 4,788 GB.
  • Both together: 516 + 4,788 = 5,304 GB.

The .bak folder is about 4,788 / 516 = 9.3 times the size of the database files it protects. If the dumps are not in the server backup at all, the same server backup needs about 516 GB.

How do I check this on my own system?

Find what the folder holds. From Microsoft's PowerShell documentation. Get-ChildItem advises -LiteralPath with -Recurse rather than -Path:

Get-ChildItem -LiteralPath D:\SQLBackups -Recurse -File | Group-Object -Property Extension -NoElement | Sort-Object -Property Count -Descending
Get-ChildItem -LiteralPath D:\SQLBackups -Recurse -File | Measure-Object -Property Length -Sum

The first counts files by extension (the pattern in the Group-Object page); the second adds up their sizes in bytes. Compare the total with the used space of the database files.

Find which paths SQL Server dumps to, and how big the dumps are. Microsoft's backupset page shows a query joining msdb.dbo.backupset to backupmediafamily:

SELECT bs.database_name, bs.type, bs.backup_start_date,
       bf.physical_device_name,
       bs.backup_size, bs.compressed_backup_size
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf ON bs.media_set_id = bf.media_set_id
ORDER BY bs.backup_start_date DESC;

physical_device_name is the device path (Microsoft notes it "may contain the original backup destination path"), backup_size is in bytes, and the ratio backup_size / compressed_backup_size is the compression ratio. A device_type of 2 means disk. If paths on a covered volume appear, those dumps are inside the server backup.

Ask the backup tool or provider:

  • Does the job cover the volume that holds .bak files, and can folders or volumes be excluded?
  • How is stored size counted for a file that is new every night?
  • What is the retention for the server backup compared with the retention of local dumps?
  • Which copy would be used first for a database restore, and has that restore been tested?

Which option should I choose?

Choose by what you want a restore to rely on:

  • If the server backup is the main protection and dumps are only a convenience, drop the dumps from the covered volumes (separate excluded disk or direct to a share), and keep the server backup's retention.
  • If dumps are the main protection for the databases, send them somewhere off the server and keep them out of the server backup, with the retention that fits recovery needs.
  • If both are required, accept the cost shown in the example, or reduce it by making the dump uncompressed so the store can match it to the previous night, but test this with your own tool because uncompressed dumps are larger on disk.

Retention clean-up of local dumps is a separate fault; the linked article on the Maintenance Cleanup Task covers a cleanup that does not run. Note Microsoft's own wording that the task "removes files locally stored on the server": it cannot trim what a server backup already took.

Risk: Excluding the dump folder or its disk from the server backup removes one protected copy of the dumps. If the dumps then exist only on that server, losing the server loses the databases' backups too. Before excluding, confirm another copy exists off the server and that a restore from it has worked. If you exclude the wrong path, the next server backup simply includes it again once the exclusion is corrected; earlier points taken without it stay without it.

Risk: Reducing local retention deletes dump files from the server's disk. If the dumps are the only copy, those older restore points are gone for good. Check the server backup or an off-server copy holds what you need first.

Risks and limits

  • These commands have not been run or lab-tested. They come from the SQL Server and PowerShell pages listed below and apply to SQL Server 2017 to 2025.
  • Microsoft's pages do not describe how a deduplicating or incremental backup stores nightly compressed .bak files. The "almost nothing in common" assumption is an expectation, and the example depends on it.
  • Backup products differ in how they exclude folders, treat open database files and count stored size. Check your own product's documentation for each of these.
  • Backing up to Azure Blob Storage or a share has its own cost and access rules, which are not covered.
  • The example's figures are made up to show the arithmetic.

Sources