You know that backups are important, so you setup a basic command like the first one below, schedule it in SQL Agent, and you have backups being performed. In between the backups, work is being done. Probably more than one INSERT, but this is just to show something is happening in the database.

Nothing. No data. Why not? If you look, you’re last insert (row “g”) occurs after the delete and before the backup. Why isn’t it in the restore?

The answer comes from a few sources. If we read the BACKUP page in Books Online (BOL), we find that if we don’t include the INIT option for a disk file, the backup is appended to the current file. The phrase in BOL is:

“If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device. ”

If we look at the INIT argument, we see that the default is NOINIT

“NOINTI – Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.”

This means that we’ve essentially done this:

Our one file, MyBackup.bak, contains 4 full backup files. This file is larger than it needs to be, and also it poses a risk. If I lose this file, I don’t lose one backup, but I lose 4.

Can I check this? Sure. Run this:

RESTORE HEADERONLY FROM DISK = 'MyBackup.bak'

I get these results:

You can see there are four files, with a “position” that differs.

Now, on the restore, why didn’t I get one row back in my table? The insert for row “g” occurred before the last full backup (backup 4), so why wasn’t it restored?

“When not specified, the default is 1, except for RESTORE HEADERONLY in which case all backup sets in the media set are processed. For more information, see "Specifying a Backup Set," later in this topic.”

The backup that was restored was our first backup, made before we did any work (inserted any rows).

What do we do? Well, we have a few choices. The last (fourth) backup would only get us the one row. If we restore the third backup, we lose the data in rows “e” and “g”. That’s usually what we want to do, so let’s restore that backup:

That gives me two rows back. I’ve lost some work, but I potentially have recovered more in many situations.

Ideally I could recover more if I had transaction log backups, but that’s another blog.

The main thing to be aware of here is to use the INIT command, write your backups to separate files, preferably with the timestamp in the file name. If you’re not sure how to do it, a maintenance plan can do it, or there’s a great script on SQLServerCentral that can help.