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

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'

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.

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.