How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to

space constraints I have to move one of the database(mdf & ldf) to

second server and access the same from the 1st server.

The following are the steps I ned to follow on the 1st server:

Detached the database

Moved the mdf & ldf files to 2nd machine/server

Attach the database pointing to the 2nd server.

The 1st and 2nd steps went fine, the problem is with the 3rd step.

In the Attach Databases window, I clicked on ADD, it opened LOCATE

DATABASE FILE, the folder structure of the 1st server. Is there any way

I can change the SELECTED PATH and poin to the 2nd server?

I did even mapped network drive to the 2nd server but the mapped drive

is not getting displayed in the LOCATE DATABASE FILE folder structure.

Can we attach mdf/ldf files which reside on other machine?

Thanks in advance.

No - you can't use mdf, ldf or ndf files on devices which are not directly attached to SQL Server. It can't resolve shares, drive mappings, or redirects at this low a level.

Buck Woody

|||Network databases are normally not recommended. Unless your network storage meets strict I/O requirements, it's _not_ supported.

That's said, you can mount a network database if you enable trace flag 1807.

http://support.microsoft.com/kb/304261
http://msdn2.microsoft.com/en-us/library/ms176061.aspx
http://www.microsoft.com/sql/alwayson/default.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to space
constraints I have to move one of the database(mdf & ldf) to second server
and access the same from the 1st server.
The following are the steps I ned to follow on the 1st server:
1. Detached the database
2. Moved the mdf & ldf files to 2nd machine/server
3. Attach the database pointing to the 2nd server.
The 1st and 2nd steps went fine, the problem is with the 3rd step.
In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
FILE, the folder structure of the 1st server. Is there any way I can change
the SELECTED PATH and poin to the 2nd server?
I did even mapped network drive to the 2nd server but the mapped drive is
not getting displayed in the LOCATE DATABASE FILE folder structure.
Can we attach mdf/ldf files which reside on other machine?
Thanks in advance.> Can we attach mdf/ldf files which reside on other machine?
Short answer: No.
Long answer: Yes, with the proper trace flag, but you don't want to do that (trust me). See
http://support.microsoft.com/?id=304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mallykarjun" <mallykarjuna@.mallykarjuna.com> wrote in message
news:5e451cd9d0a042e2b8571d9eee45d779@.ureader.com...
> SQL Server 2005 and the related databases are on one server. Due to space
> constraints I have to move one of the database(mdf & ldf) to second server
> and access the same from the 1st server.
> The following are the steps I ned to follow on the 1st server:
> 1. Detached the database
> 2. Moved the mdf & ldf files to 2nd machine/server
> 3. Attach the database pointing to the 2nd server.
> The 1st and 2nd steps went fine, the problem is with the 3rd step.
> In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
> FILE, the folder structure of the 1st server. Is there any way I can change
> the SELECTED PATH and poin to the 2nd server?
> I did even mapped network drive to the 2nd server but the mapped drive is
> not getting displayed in the LOCATE DATABASE FILE folder structure.
> Can we attach mdf/ldf files which reside on other machine?
> Thanks in advance.

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to space
constraints I have to move one of the database(mdf & ldf) to second server
and access the same from the 1st server.
The following are the steps I ned to follow on the 1st server:
1. Detached the database
2. Moved the mdf & ldf files to 2nd machine/server
3. Attach the database pointing to the 2nd server.
The 1st and 2nd steps went fine, the problem is with the 3rd step.
In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
FILE, the folder structure of the 1st server. Is there any way I can change
the SELECTED PATH and poin to the 2nd server?
I did even mapped network drive to the 2nd server but the mapped drive is
not getting displayed in the LOCATE DATABASE FILE folder structure.
Can we attach mdf/ldf files which reside on other machine?
Thanks in advance.> Can we attach mdf/ldf files which reside on other machine?
Short answer: No.
Long answer: Yes, with the proper trace flag, but you don't want to do that
(trust me). See
http://support.microsoft.com/?id=304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mallykarjun" <mallykarjuna@.mallykarjuna.com> wrote in message
news:5e451cd9d0a042e2b8571d9eee45d779@.ur
eader.com...
> SQL Server 2005 and the related databases are on one server. Due to space
> constraints I have to move one of the database(mdf & ldf) to second server
> and access the same from the 1st server.
> The following are the steps I ned to follow on the 1st server:
> 1. Detached the database
> 2. Moved the mdf & ldf files to 2nd machine/server
> 3. Attach the database pointing to the 2nd server.
> The 1st and 2nd steps went fine, the problem is with the 3rd step.
> In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABAS
E
> FILE, the folder structure of the 1st server. Is there any way I can chang
e
> the SELECTED PATH and poin to the 2nd server?
> I did even mapped network drive to the 2nd server but the mapped drive is
> not getting displayed in the LOCATE DATABASE FILE folder structure.
> Can we attach mdf/ldf files which reside on other machine?
> Thanks in advance.

How to attach a MS SQL 2005 database (.mdf) file to MS SQL 2000

I have 2 files from MS SQL 2005, .mdf and .ldf . How am I going to attach it to the MS SQL 2000? Does it need converter? Please guide.
Thanks.

Quote:

Originally Posted by kentwong

I have 2 files from MS SQL 2005, .mdf and .ldf . How am I going to attach it to the MS SQL 2000? Does it need converter? Please guide.
Thanks.

with enterprise tools u can attach the datbase|||You have posted this question in the Articles section. I am moving it to the SQL Server forum.

ADMIN

how to attach a .mdf file?

You also need the ldf file. Are you clicking the Add button in the attach
dialog box to browse for the file you want to attach?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u1RLbzeLHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Hi,
> i installed sqlserver express 2005.
> I also use the sql server management studio express. I can connect to
> (myserver\sqlexpress), but when i want to attach any existing .mdf file on
> my disc, the window remains empty and i have not the opportunity to
> specify a path.
> Any idea how to fix this?
> Thanks
> Bob
>
Sounds like a security issue. When you attach through the debugger you
start up a user instance which attaches the database as the user who is
doing the debugging. When you attempt to attach using sqlcmd or the
workbench, you are attaching to the normal instance which runs as
networkservice by default. The problem is probably that network service
doesn't have write permission on the mdf and ldf files for your database.
There's more information about user instances here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bob" <.> wrote in message news:u6TZOGoLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks for replying. I thing i become crazy. I desinstalled and
> reinstalled it 2 times, but it still doen't work.
> Yes, i have the ldf file.
> When i click on ADD button, i get an error telling me that "cannot access
> the sprecified path etc ...Verify that you have specific privileges ..."
> I tried to add "myserver\aspnet" as account (server properties,
> permissions), but then, it's always un recognized ...
> I did sqlcmd -S myserver in the dos-box and i get:" name pipes provider:
> could not open a connection to SQL Server"
> And when i start an asp.net application, i get the error: "
> An attempt to attach an auto-named database for file C:\myapp\my.mdf
> failed. A database with the same name exists, or specified file cannot be
> opened, or it is located on UNC share.
> The only way it works is when doing ctrl-f5 in an application within VWD.
>
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> schreef in bericht
> news:uKalp4eLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>