Link to home
Start Free TrialLog in
Avatar of techgyan
techgyan

asked on

Change SQL data base location

can we change the location of SQL server database from attached hard disk drive to NAS?
Avatar of WayneATaylor
WayneATaylor
Flag of United Kingdom of Great Britain and Northern Ireland image

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



ASKER CERTIFIED SOLUTION
Avatar of j_meca
j_meca
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Ephraim Wangoya

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

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

Wayne

it´s a joke