move db to another drive

[jdeane]

Hi

complete newbie when it comes to SQL administration so here goes:-

we are running out of disk space on the drive where all the db's are located, is there an easy way to move one or more db's to another drive without to much hassle?

How can I find out the size/location of the db's so I can decide which is the most appropriate to move?

Thanks

Jon

 

[JayKusch]

Yes ... in Enterprise Manager, right-click on the DB that needs to be moved. Choose "All Tasks" .. "Detach Database".

Once that is done move the MDF and LDF file of the database to it new home on another drive. Then ...

in Enterprise Manager, right-click on the DB that needs to be moved. Choose "All Tasks" .. "Attach Database" and point it to the new folder the files have been moved to.



Thanks

J. Kusch

 

[hmscott]

Hi,
You actually have a couple of options.

1. You can use sp_detach_db to detach the database from the server. Then move the files using any MS windows tool (command line, Explorer, etc). When the file is back to where you want it to be, use sp_attach_db to re-attach the files.
2. You could simply add another file (in another file group) that is located on a volume where you have sufficient additional capacity. Then set a maximum limit on the PRIMARY file group. Look at FileGroups in Books On Line for more information as there are a couple of nuances to this option.
3. You could back the database up, delete it and restore it to a different location (I don't swear that this will work).

HTH,

hmscott