choose append to existing backup instead of overwrite

Question

I have a database and I made it's first backup 2 days ago. Then yesterday I spent an entire adding new records. This morning I ran a backup, (but I selected append to existing backup set) as pictured below.

I just ran a restore and I found that it wiped out all my data from yesterday and it restored it from the backup of 2 days ago. Not the version from this mornings backup.

I zipped this backup file to be safe.

I changed some data in the DB, Then I ran the back up again, but this time I selected "overwrite all existing backup sets"

Now when I restore the db it's seems to restore the data from the backup correctly. I think I learned a lesson here, correctly if I'm wrong

My questions is, Did I lose an entire day of work?

I still have this morning's backup .bak file safe in a zip. Is there anyway I can restore is with the right data?

alt text http://img62.imageshack.us/img62/1934/backup1.gif

Solution

The good news is you didn't (or at least shouldn't have) lost your work. By selecting the "append" option (which is default behavior) all that happened was the most recent backup was appended to the end of the last backup. So, on the same backup file you have 2 backups.

When you restore from the backup file, if you're doing this through the GUI you should get the option to select the specific backup set you want to restore - and there should be two backup sets you could restore from.

If you were doing this through T-SQL the restore command would look some like:

RESTORE DATABASE [my_db] FROM  DISK = N'C:\my_db.bak' WITH  FILE = 2...

The key here being the "FILE = 2" option that is specifying which backupset you want to restores.

Hope that helps you get your data back.

Comments

[aron]

Yes, and I found that Red Gate Data Compare allows you to compare a backup. And then select the backup set inside the backup. cheers

[Jake]

This happened to me but I didn't see the GUI option. I was only able to restore the database using the command you provided (FILE = 2 as you indicated above). Thanks!