Mapping SQL server mdf and ndf to a NAS

Hi I am creating a new DB on 2008R2 via SSMS.
When I want to change the mapping of the files to a network share (NAS), the folder tree just shows the LOCAL disk, and not any Network folders.

How can I map the new db files to a network share?

Thank you,
Noa
noamco36Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Step 1 details:

ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_file_name1, FILENAME = 'x:\new\path\to\file\filename.mdf' )

ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_file_name2, FILENAME = 'x:\new\path\to\file\filename_log.ldf' )

...[one ALTER command for every file]...


Note that until you set the db offline (or detach it), or SQL stops and restarts, the db can continue to be used normally in its current location.
0
 
micropc1Connect With a Mentor Commented:
I very much would not recommend running a production database from a network location. You will not only lose performance, but increase your risk of database corruption. That said, you'll need to detach the database, move the files, and attach them using the CREATE DATABASE... FOR ATTACH command like shown here...

http://technet.microsoft.com/en-us/library/ms187858.aspx
http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx

You may also find this useful...
http://sqlrecoverydatabase.blogspot.com/2010/03/storing-sql-server-database-on-network.html?m=1
0
 
noamco36Author Commented:
Thank you.

If it is not recommended to run database from a network, then:
1- how do you manage space disk issues if you create the new db on the defualt location (usually C:\)  and C:\ keeps on growing in size?
2- why do many companies use SAN for storing their databases?
3-what about if others need to connect to your databases and if your local machine is turned off?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
micropc1Connect With a Mentor Commented:
To answer your questions...
1. Add more disk space or use better measures to manage your database growth
2. A SAN (Storage Area Network) is not the same as a NAS (Network Attached Storage). SANs offer better performance as they are typically implemented with a dedicated connection to the server using fibre channel or iSCSI - unlike a NAS, which is basically just a windows share on your TCP/IP network. They are also much more costly than a NAS.
3.This made me curious... Is SQL Server running on a Workstation? If so, and this is a heavily-used production database, you should consider moving it to a server.  A database can only be attached to a single instance of SQL Server at any given time, so you would need to detach and re-attach the database every time you wanted to host it from a different instance of SQL Server. Your best option would be to host it on a high-end server that is always on.
0
 
Eugene ZConnect With a Mentor Commented:
About Sql server on NAS
check

Description of support for network database files in SQL Server
http://support.microsoft.com/kb/304261
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/18b9c0ef-b276-4cfa-8506-ef36f12055b1


if you use SAN -  you can use mount points :
http://www.sqlservercentral.com/Forums/Topic609005-146-1.aspx


more about MP
Use mount points if you run out of Windows drive letters
http://www.techrepublic.com/blog/datacenter/use-mount-points-if-you-run-out-of-windows-drive-letters/383
0
 
Eugene ZConnect With a Mentor Commented:
and about mapping to NAS and SQl Server
SQL Server Can Run Databases from Network Shares & NAS
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can do it, but first you have to set a trace flag 1807 on.

Then:

1  ALTER the database to "tell" SQL about the new file locations
2  List the sys.master_files table for your db, to verify that SQL "knows" about the new file locations
3  ALTER the database OFFLINE --do NOT detach
4  Physically copy (or move) the files to the new drive locations
5  ALTER the database back ONLINE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.