How to attach an .MDF file to another server?

Hi all

I am having trouble moving an .mdf file from a dev machine to a production machine.

On the dev machine(windows xp sp2 login <MyName>) I have an SSE instance - part of VisualStudio installation.
on the production machine I have only one full version SS instance called "PROD"(windows server 2k3, login Administrator) I do have SSMS installed too.

I create a website in VS on my dev machine. C:\Websites\Website1
I add to App_Data a new file: DB1.mdf
I add a couple of tables to DB1.mdf and maybe other objects etc. - all dbo.
Then I detach DB1.mdf and physically copy the file (without the .ldf as suggested in books online) via tha LAN.

from: C:\Websites\Website1\App_Data\DB1.mdf(dev machine)
to: C:\DB1.mdf (production machine)

I then try to attach C:\DB1.mdf to PROD and I get an error. The server is looking for a db by the name of the original path on the dev machine C:\Websites\Website1\App_Data\DB1.mdf There seems to be no way to rename the db.-not in VS before detaching it, not on PROD in the GUI to attach.

Just for kicks, I created the same folders on the production machine, so the file would have the same physical path as its name(which I still cannot change). C:\Websites\Website1\App_Data\DB1.mdf I even pasted the original .ldf file. This time it worked. I was able to attach it.

What is up with this? Is an .mdf file for ever stuck with the name of the path where it was first created?

This seems to be a most common scenario, even though I am not deploying a whole website.

Are we always supposed to execute our own script?

How are you reattaching the file ?

You should be able to use the command:

CREATE DATABASE database_name
ON
(name = logical_file_name,
filename = 'new file location')
FOR ATTACH

You'll need to know the logical_file_name that the mdf file uses. Find that out by running this command in the database before detaching:

select name from sysfiles where filename like '%.mdf'
|||

If you right click on the databases folder in Object Explorer in SSMS and select Attach... you get a UI to locate the .mdf file. The UI looks for the other files in database (.ndf or .ldf) in the locations specified in the .mdf file. If it doesn't find them there, it displays an error message for the files it can't find.

You can change the location where it is looking for the other files by editing the "Current File Path" in the lower grid. You can also remove the .ldf files from the grid (so it won't try to attach them) by selecting them in the grid and clicking on the Remove button.

Hope this helps,

Steve

|||

Thanks to both of you.

Both ways work now. I can't even duplicate the issue, and I remember I struggled for a while prior to posting a few days ago. It just didn't like that file name. I swear there is a voodoo ghost on the network!

Steven, I also played with some test UDFs and TVFs written in c#. I right-click deployed the assembly in visual studio. The test was to see what all I needed to do when I reattach the .mdf file. Well the functions work on the new machine just like that. So where is the .net assembly? Is the .dll embedded in the .mdf file? Because that is all I transferred.

Carl