Re attaching a db 2

[btacy]

hi,
i had to create a test database(C) from a backup of a present one(B). while trying to restore C from the backup of B , it kept giving errors saying the datafile was in use, so i took B offline and tried again, the same thing happened and then i detached B and tried once more and it worked. the problem now is i'm trying to re attach B and it gives an error " Create file encountered operating system error 32(error not found) while attempting to open or create the physical file '...B.mdf path..'

i have a feeling that i'm having this error because the db was offline before i detached it, but i'm not sure and i don't know what to do in order to get things back to normal.

i'd appreciate any help, thanks.

 

[Ovatvvon]

When you are restoring the databases, you have to rename the physical file name of one of them (B or C) on the file system. You can do that under the Options tab of the restore database dialog window.

If you don't do this, it tries to create the database with the same file names. You can't have two files in the same directory on any windows machine with the same file name. The only time you would do this is if you were replacing the current database with the BAK file, in which case you would need to check the checkbox 'restore over current file' (<-- title from memory, didn't go verify the actual checkbox text)

hope this helps.

-Ovatvvon :-Q

 

[mrdenny]

Well, you now need to restore the origional database (B) from the backup you have as you have wiped out it's data files when you restored the files for C over the files for B. After you have done that you need to restore C again but this time moving the files as Ovatvvon has said. I hope that your backup of B is current.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

Indeed, I overlooked that part. Good catch Denny.

-Ovatvvon :-Q

 

[btacy]

thanks for your responses.
mrdenny, the database B is detached, so i can't even restore it from a backup. any ideas how i can restore it without it being attached or overcome the error it gives when trying to attach it?

 

[btacy]

thanks a lot Ovatvvon and mrdenny. your responses helped me get my head around the problem.

i have resolved it and here is what i did:

i took C offline and tried to attach B again and it re attached successfully, then i restored C from a backup of B and renamed the files as Ovatvvon pointed out. and both databases are up and running well.

i really appreciate your help.

 

[mrdenny]

Do keep in mind that any data that you had in B that wasn't in the backup that you restored as C is gone.

You don't have to have a database there with the name of the database to do the restore. Simply type in the name of the database to restore to in the drop down box, or use the T/SQL RESTORE DATABASE command.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog