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:
|
1 2 3 |
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:
|
1 2 3 |
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.
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
You are most welcome !