Trying to restore a database.

Question

Trying to restore a database.

Isabelle2378

SSCertifiable

Points: 6174
More actions
November 22, 2005 at 11:58 pm

#111521

Hi,
Please go easy on me....I come from an Oracle World.
A user deleted some data and I'm trying to restore the database up to a
certain point in time. My backups are going to a network drive and I see the
backup and log files that I want to use for my restore. Before I started the
restore, I issued a manual complete backup of the database to a local drive
on the server in case anything went wrong with the restore. Here are the
steps I took:
1) detached databaseA and re-attached with name databaseA_old
2) created a new database with name databaseA
3) used Enterprise Manager - all tasks - restore and it only showed the last
backup that I did to the local drive. It did not show any of the previous
backups to the network drive. I navigated to the network drive and see all
the backups, but they do not show up in the restore window. Why couldn't I
see all the previous backups?
So, I aborted the restore and changed the name back from databaseA_old to
databaseA (using detach and attach). Also, after all this, when I looked at
my maintenance plan, databaseA was no longer checked to be backed up. Is
this related?
Thanks!
Bea
Thanks!
Bea Isabelle

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

When you restore a database, by default in Enterprise manager shows the last back up you did. The UI has a drop down list "First backup to restore". From there you can select previous backups.

I am not sure about why the database is not marked to back in the maintenance plan. One possible reason is when attached the database, its databaseid is changed. The new database id is not in the maintenance plan backup list.

peterhe SSChampion Points: 11469 More actions · Answer 2

By the way, if you just want to change the name of a database, you donot need to detache and re-atche it with a differetn name. You can run sp_renamedb to change it.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 3

Good Morning,

The drop down list does not show all of the older backups, including the one I need to use to restore. I have a test box with SQL on it and it shows me all the backups via that drop down list but not on my production server. How can I tell it to navigate to my backup paths and choose the backup I want if it's not on that list?

Also, thanks for the tip on the database rename!

Bea

Thanks!
Bea Isabelle

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 4

Hi,

Okay, I manually copied the backup to the directory that shows up on the drop down list and I see the backup that I want to restore. The backup is of CompanyT and I want to restore it as CompanyZ. I changed the logical and physical names of the files to CompanyZ but I get this error on restore:

Logical file 'CompanyZ' is not part of database 'CompanyZ'. User RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.

Now I am using only a full backup to restore. I am not bringing over any additional log files. I created an empty CompanyZ database and I'm trying to get the restore to overwrite this database with the backup file. Is this correct? Or can I restore from CompanyT backup to a CompanyZ database without CompanyZ existing (the restore will create it?)

Thanks!

Bea

Thanks!
Bea Isabelle

peterhe SSChampion Points: 11469 More actions · Answer 5

Yes, you don't need to create CompanyZ. You can restore from ComanyT backup to a database with any name and the restore process will create it. You cannot change the logical file name during the restore.

Differetn databases can have same logical file names.