Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Change SQL data base location

Posted on 2011-10-04
8
Medium Priority
?
210 Views
Last Modified: 2012-05-12
can we change the location of SQL server database from attached hard disk drive to NAS?
0
Comment
Question by:techgyan
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:WayneATaylor
ID: 36908771
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36908779
0
 
LVL 1

Accepted Solution

by:
j_meca earned 2000 total points
ID: 36908786
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 37

Expert Comment

by:Neil Russell
ID: 36908907
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36914061

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

Personally, I prefer backup and restore
0
 
LVL 1

Expert Comment

by:j_meca
ID: 36917315
thanks!!! 100 users have ended up with me in case of not working
0
 
LVL 10

Expert Comment

by:WayneATaylor
ID: 36917415
I don;t understand that last comment from j_meca!!

Wayne

0
 
LVL 1

Expert Comment

by:j_meca
ID: 36917620
it´s a joke
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question