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

Big building

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:

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:

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

 

 

2 thoughts on “SQL: Why do my SQL Server backup files keep getting much larger?”

  1. Hi Greg, thanks for reminding me again about backups that gets forgotten over time. Currently have Ola's solution set with defaults for @Format=N and @Init=N so will review them again across our database estate.

    Cheers
    Kane

Leave a Reply

Your email address will not be published. Required fields are marked *