Change SQL data base location

can we change the location of SQL server database from attached hard disk drive to NAS?
techgyanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WayneATaylorCommented:
You can change this, but the actual process does depend on what version of SQL and what type of NAS.

If this is an iSCSI based NAS then you will have a drive letter on the server that can be used but SQL running as a service.  If it's just a share based NAS, i.e. you access it using a UNC path e.g.  \\server\share or a mapped drive to a UNC path, then it's not really possible.

The best way is to detatch the database from the server, copy the [hysical files from the DATA directory, making sure you get all the files for that database logs and data etc and then copy the files to the new location.  You can then attach then using the SQL manager.

Wayne



0
Neil RussellTechnical Development LeadCommented:
0
j_mecaCommented:
of course the database can reside on any drive if you have connection from the server where SQL is installed. But do not just copy the MDF, you need to back up and restore in a new one with other name, this one must be placed it in the NAS disk, then delete the original and rename the new one.

I mean, your database is called, for example, CLIENTS.... back up it, now in databases click right and create a new one.... you can call it CLIENTSNEW, in General options point it to the NAS drive route (LOG and database, if you have both on the same volume). In options you have to put it in simple or full mode and accept.

Now in databases click right button and choose restore, in the new window you have two options: to database:CLIENTSNEW and From database/device: tag device and look for the place where you backed up CLIENTS (.bak), tag it. Go to options, in the same window, and mark overwrite with replace and "restore with recovery"... Accept and ready. When it finishes delete the CLIENTS and rename CLIENTSOLD as CLIENTS

good luck!!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Neil RussellTechnical Development LeadCommented:
Sorry j_meca, your wrong.

It is a microsoft supported method to use

use <database_name>
go
sp_helpfile
go

use master
   go
   sp_detach_db 'mydb'
   go
(Move files to new location/drive)
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
  go

use mydb
   go
   sp_helpfile
   go

As per the MICROSOFT article posted above.
0
Ephraim WangoyaCommented:

You can do it whichever way you like, either detach and attach, or backup and restore

Personally, I prefer backup and restore
0
j_mecaCommented:
thanks!!! 100 users have ended up with me in case of not working
0
WayneATaylorCommented:
I don;t understand that last comment from j_meca!!

Wayne

0
j_mecaCommented:
it´s a joke
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.