Friday, March 30, 2012

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

No comments:

Post a Comment