techgyan
asked on
Change SQL data base location
can we change the location of SQL server database from attached hard disk drive to NAS?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\mydbdat a.mdf','E: \Sqldata\m ydblog.ldf '
go
use mydb
go
sp_helpfile
go
As per the MICROSOFT article posted above.
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\mydbdat
go
use mydb
go
sp_helpfile
go
As per the MICROSOFT article posted above.
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
Wayne
it´s a joke
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