Why is .bak file size increasing/doubling with each consecutive backup on SQL Server?

Question

I'm running a simple DB on SQL Server 2012 Express.

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.

Answer 1

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":

Answer 2

I'm assuming you are backing up to the same file each time. There is an option to INIT or NOINIT when you run a backup. NOINIT (which is the default) tells it to just add an additional backup to the file.

Per BOL:

{ NOINIT | INIT }

Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

So each time you run your 55MB backup the file gets ~55MB bigger.

If you include the INIT clause in your backup command then it will overwrite the existing backup each time.

The other option of course is to run your backups into new files each time and clean up the old files. This is frequently a good idea in case one of the current backups is bad, or you need to recover into history.

Answer 3

If you are doing it straight in SQL not using sql server management studio wizard. You have to specify "NOINT" or "INIT" when backing up to say add to the file or overwrite it respectively.

Here is the SQL for it

BACKUP DATABASE <data_base_name> TO DISK = <File_Path> WITH INIT