move the database file to another location

I have the database on the SQLDBSRV server but the sapec is not enough for the data and I want to move the file to another server SQLFILESRV without moving the whole server.. I mean just the data file.. Is that possible?

Here is a Microsoft Knowledge Base article on how to move a database using the Detach/Attach method: http://support.microsoft.com/default.aspx/kb/224071/

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

but ATTACHing the database means it will copy the file into the Data folder on the server? right?

What I want is having the actual sql server on ServerA and the data file on ServerB

|||

Hi,

AFAIK it is not possible to have database files on another server /mapping a files because it should remain on the same server and its not the best practice also this would lead to severe issues also like

NETWORK Bandwidth Consumption

Backingup/restoration

if in case it goes in suspect / offline

|||

It's possible to move the database file to another location and ATTCHing it to the server which will not actually move the data and log file to the default Data directory but instead point to the new location and hence there is no redundancy.

This works well until the database file are located into the same server, irrespective of the location in the server. But it's not possible to attach a database whose data and log files are located to another server location as the sp_attach_db SP throws an error : "The location of the mdf file on a network path is not supported for datbase files" while giving the path of the database file on another server location.

Regards,

Rajesh

|||

then is it possible to map the network drive so it will be D or E on the server and move the file to it?

another questions plz: what files should i move? just the mdf file?

|||

Hi Jassim,

I tried mapping the share on another server to a local network drive but it doesn't works. I guess it's not supported as of now.

You can report this issue through MSDN product feedback center by selecting “Send Feedback” menu item under “Community” Menu in SQL Server Management Studio. You can also file bugs directly on http://connect.microsoft.com/sqlserver/feedback which will help you to track the status of the bug.

Regarding your second question, it would be good if you move mdf as well ldf files to one common location.

Regards,

Rajesh

|||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.mspxsql