How to Re-attach database?

[lorirobn]

Hello,

I was trying to copy a database from an existing database into a new database on the same server. I followed the advice in a posting, saying to detach the existing database to be copied, copy the .mdf/.ldf files to wherever you want them, then attach the "new" .mdf/.ldf files to a new "empty" database, and then reattach the original.

I was able to detach the original database in SQL Server. I copied its .mdf and .ldf files within my MSSQL folder. I set up a new blank database in MS SQL Server. But - I do not see or know how to either attach the new database or re-attach the existing database. Am I missing something? Am using MS SQL Enterprise Manager. In 'Action' --- 'All Tasks', there is a Detach option, but no Attach option.

Please help! thanks....

 

[lorirobn]

PS - my original database is gone from the SQL Server Enterprise Manager list of Local databases.

 

[jbenson001]

You don't need to create a blank DB first. Just right click on the DataBases folder under the Server Name in the tree. Then click on All Tasks -->> Attach Database.

Jim

 

[SQLBill]

You cannot set up a blank database and then attach one. Delete your blank database. Then as jbenson says, just go to All Tasks and attach your database.

-SQLBill

Posting advice: FAQ481-4875

 

[lorirobn]

ok, done, partly. I deleted the blank db, reattached the old. But in trying to attach the new database (which there is none, only copied mdf files), I browse for the names, select the new mdf files, but EM fills in the original file name and location of the original mdf/ldf in this pop-up window. I cancel out of the 'attach' action as that doesn't look too good.

So how can I set up this copied database?

 

[SQLBill]

Are you trying to attach BOTH versions to the same SQL Server instance? I thought you were copying and moving the files to a different server.

-SQLBill

Posting advice: FAQ481-4875

 

[jw2000]

If you are just looking to backup your database, detach then copy the ldf and mdf files into another directory. Copy these files back into the orignal directory, then attach the mdf file.

If you are trying to attach both versions to the same SQL Server instance, let me know. Hope this helps.

 

[lorirobn]

Hi,
Yes, I was trying to attach both to my Local server, within same SQL Server Group. Got it to work by backing up the original, and called it the New Version when asked to give it a name.
I think what I did wrong was to copy the ldf and mdf files to the same directory???
Anyway - it's working ok... for now!
Thanks for all your help.

 

[jw2000]

Great ... you're welcome.

The problem is likely that the original and copied lfd / mdf files are in the same directory. I did the same thing a few months ago and everything has been working.

 

[lorirobn]

Hi... it's been awhile, but if you are still out there, could you tell me how to attach both versions to the same SQL Server instance instead? All I am trying to do now is back up my database to another database before I start making a lot of changes to it.

thanks...

 

[jw2000]

I don't remember if I was able to attach both versions to the same SQL Server instance, and I don't suggest doing this.

If you want to back up your database before making a lot of changes to it, you should copy the original mdf / ldf files into another directory. These will be your backup files.