misunderstanding recovery from backup 2

[esmithbda]

I am not an MCDBA, so there are likely many things that I do that are not technically correct, but work well enough to get the job done. I am a developer on a database project without a proper DBA position, so I get to play that role.

The following happened, and I suppose it is a sign that my understanding of how backup/recovery processes on MS SQL Server 2k work is not accurate, so I was hoping the experts here could set me straight.

1) I go to Enterprise Manager, right click on a database (call it DatabaseA), select AllTasks->Backup Database...
2) I do a complete backup and append over to the existing backup file for that database

In my mind, that means that as of that backup, I am taking a complete backup, and tacking it on to a series of other complete backups that I have done in the past. Day1, Day4, Day20, whatever, they are all there, but separate from each other.

Then I go to restore a backup into a new or existing database to use it as a test, say DatabaseB.
1) right-click on DatabaseB (assuming it already exists), select All Tasks-> Restore Database...
2) select to restore as DatabaseB, but restore from DatabaseA backups

When I do this, I get a copy of the most recent backup of DatabaseA, and it works great.

But yesterday, I wanted to restore a backup from several weeks ago.

So I did the restore, but in the list of possible backup sets to use, I unchecked everything but an older date and then ran the restore.
No matter what I selected, it would give me the most recent backup - data from that day that I had just backed up. But I didn't want that and wanted a copy of the database from a few weeks ago - which showed up in the list of appended complete backups.

I even deleted DatabaseB and created it new and then restored into it in that way - still had the newest data.

Can someone please explain to me how I am wrong and what I can do to get the desired effect of older data?
Do I need to not do an append, and instead go out to separate files for each backup, if I want to later recover to that point?

Thank you

 

[yelworcm]

In my mind, that means that as of that backup, I am taking a complete backup, and tacking it on to a series of other complete backups that I have done in the past. Day1, Day4, Day20, whatever, they are all there, but separate from each other.

Correct.

For the second part (restore), I am not sure what is going on, but I may have a theory. Run this command on your server via Query Analyzer:

restore headeronly from disk = 'your backup file here'

If you are always getting the most recent backup, it could be that someone or something is deleting the old backups. Perhaps to free up diskspace? Enterprise Manger defaults to taking the first (and earliest) backup in the file. Always getting the most recent one is a touch confusing.

 

[mrdenny]

When you bring up the restore window in EM select From Device, point it to your backup file.

Clock the View Contents button. This will show you all the database backups which are available for restore.

You can pick any from this list (this does the same thing as yelworcm's code).

Instead of quering the database for the list of backups, this actaully queries the backup file. If it's not on this list, then it's not available for restore.

Your best bet is to backup to a different file each time you want to do a backup. This will ensure that you have the correct backups that you are looking for, as well as give you the option to delete any backups that you don't care about any more.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[esmithbda]

Okay, so it appears that I was technically doing the right thing, and thinking of it in the right way - and it also sounds like I should modify my behavior to using new backup files in this sort of case.

Thank you both for your helpful responses - it is encouraging that I'm perhaps not as big an idiot as I felt. Or at least not on this one matter