SQL Server Backup – Why is .bak File Size Increasing?

backupsql serversql-server-2012

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.

Best Answer

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

Related Solutions

Sql-server – SQL Server backup, attach to BAK file

First there's no way using the native tools to mount a backup file and read the data in it. That said, what you can do is write your version information into the description of the backup using the DESCRIPTION keyword. Then read this information using the RESTORE DATABASE WITH HEADERONLY to get the information and make sure that they have the correct version.

SQL Server – Recover from .bak File with NOINIT

That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media. See the section on this page of Specifying a Backup Set.

Instead of:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak';
go

You will want to do:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak'
with file = <n>;
go

Where N is the backup set number in the media. N can be obtained from running RESTORE HEADERONLY on the media. The column you will be concerned with is Position to use with the FILE option.

Best Answer

Related Solutions

Sql-server – SQL Server backup, attach to BAK file

SQL Server – Recover from .bak File with NOINIT

Related Question