SQL: Why do my SQL Server backup files keep getting much larger?

Another question that I keep seeing on SQL Server forums is:

Why do my SQL Server backup files keep getting much larger?

The backup commands that the poster is using look like this:

BACKUP DATABASE @DatabaseName
    TO DISK = @BackupFile
    WITH COMPRESSION;

When you do a backup, what you’re actually doing is adding a backup set to a media set. Each time you do a backup like that, you’re adding another backup set to the same media set.

So, what the poster is missing is that the default for SQL Server backups, is to APPEND to the operating system file. So more and more backups are now being included in that file.

If you want the file to only include the single backup, and overwrite what’s already there, you can do this instead:

BACKUP DATABASE @DatabaseName 
    TO DISK = @BackupFile 
    WITH FORMAT, INIT, COMPRESSION;

INIT overwrites the backup file but keeps the media header. FORMAT overwrites the media header as well.

2020-05-28