patternsqlModerate
Why is .bak file size increasing/doubling with each consecutive backup on SQL Server?
Viewed 0 times
whyfileeachwithsizesqldoublingincreasingserverbak
Problem
I'm running a simple DB on SQL Server 2012 Express.
Just today, when I backup the database, the
In SQL Server Mgmt Studio, when I right click on my DB -> 'Reports' -> 'Backup and Restore events' -> expand 'Successful Backup Operations':
The report here shows that the latest backup size was recorded as 55MB, but when I go to the actual
What could be going wrong? I haven't made any changes to the database since this started happening.
Just today, when I backup the database, the
.bak file size doubles to what it was from the previous backup just minutes before. I have done several backups today (through SQL Server Management Studio -> backup type: full), and with each one the .bak file keeps on doubling. In SQL Server Mgmt Studio, when I right click on my DB -> 'Reports' -> 'Backup and Restore events' -> expand 'Successful Backup Operations':
The report here shows that the latest backup size was recorded as 55MB, but when I go to the actual
.bak file it is 260MB. Each other backup today was recorded also as 55MB size, while their corresponding .bak files are multiple times larger (and growing with each backup operation).What could be going wrong? I haven't made any changes to the database since this started happening.
Solution
The backup file contains multiple copies of the database since it is not being "initialized" or "formatted" with each backup.
Go to the 'Options' tabl on the "Back Up Database" dialog box, and select the "Overwrite all existing backup sets":
Go to the 'Options' tabl on the "Back Up Database" dialog box, and select the "Overwrite all existing backup sets":
Context
StackExchange Database Administrators Q#139776, answer score: 18
Revisions (0)
No revisions yet.