Help! My backup file has doubled in size.

2

June 16, 2016 by Kenneth Fisher

Help! I backed up my database yesterday and the backup file was about 200MB but today it’s closer to 400! What happened?

Did you know a single backup file can contain multiple database backups? When you backup a database to a file, if that file already exists, then by default the backup will be appended to the existing file. Causing the file to increase in size. If it’s the same database (and yes you can have a single file containing backups from multiple databases) then the file size will double or more. This behavior is controlled by the INIT/NOINIT clause of the BACKUP DATABASE command. NOINIT (the default) tells SQL to append the new backup to the existing file. INIT tells SQL to overwrite the existing backup files. Note the header of the file is not initialized.

So for example:

BACKUP DATABASE AdventureWorks2014 TO DISK = 'C:\backups\AdventureWorks2014.bak'
-- File size: 198,948 KB
BACKUP DATABASE Test TO DISK = 'C:\backups\AdventureWorks2014.bak'
-- File size: 337,335 KB
BACKUP DATABASE AdventureWorks2014 TO DISK = 'C:\backups\AdventureWorks2014.bak'
-- File size: 536,281 KB

multibackupfiles2

Well, that’s kind of cool but how do we check what’s actually in a backup file?

RESTORE HEADERONLY FROM 
     DISK = 'C:\backups\AdventureWorks2014.bak'

multibackupfiles1

This isn’t all of the columns by any means but there are few particularly important ones shown here. DatabaseName and BackupStartDate/BackupFinishDate help you determine which backup you actually want to restore. But that implies that you can actually restore from any of the backups in the file right? And of course you can, by using the Position field.

RESTORE DATABASE Test FROM 
     DISK = 'C:\backups\AdventureWorks2014.bak' WITH FILE = 2, REPLACE

FILE = 2 is the 2 from the Position column.

When using the GUI you select the database (if there are multiple databases backed up in the same file) then you use the timeline option to pick which of the multiple backups for that database (assuming more than one) you want to restore.
multibackupfiles3

There are a few restrictions. You can’t, for example, have one file in a backup file compressed and another one not compressed. You also can’t have one backup stripped and another one not. You can however have FULL, DIFFERENTIAL and LOG backups in the same file.

Now all of this is using very simple BACKUP and RESTORE commands. Both of these commands are actually fairly complex with a large number of options. As important as the are it makes sense to spend some time going over the commands, getting familiar with the options, and at least being able to run basic BACKUP and RESTORE commands from memory.

2 thoughts on “Help! My backup file has doubled in size.

  1. notarian says:

    One of many safe defaults in SQL Server. Instead of accidentally overwriting a backup file you might need, they append.

    • I’ll agree it’s safer that way, on the other hand it can get really confusing if you don’t know what’s going on 🙂 It’s one of those “Know your defaults” things.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: