Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

my hard-drive is currently almost full!! I need to migrate the db to a different drive, how can I do this?

They are all stored in the default C:\ drive.

Any ideas?

share|improve this question
add comment

4 Answers 4

up vote 4 down vote accepted

One approach would be to use Management Studio (Enterprise Manager in SQL 2000) to detach your databases, physically copy the mdf and ldf files to your other drive(s), then use Management Studio to re-attach them.

share|improve this answer
    
The physical copy is something you do (Windows Explorer) outside of Management Studio. –  jl. Jan 14 '11 at 17:13
    
Thank you! I tried detaching once, but it went horribly wrong... any tips on how to do it without causing problems? –  RadiantHex Jan 14 '11 at 17:22
1  
Can you be more specific - messages, etc? I assume we're talking about a database other than master, model, or tempdb, in other words a database that you created. What SQL Server version are you using? –  jl. Jan 14 '11 at 17:43
1  
@RadiantHex - define "horribly wrong". This is usually a pretty safe operation, assuming you're aware that the database will be down/unavailable from the moment you detach until the moment you finish re-attaching. –  Joel Coel Jan 19 '11 at 22:35
add comment

As long as you aren't using replication, you can simply detach the database in Management Studio, move the DB and log file to the new location, and re-attach through Studio. If you are using replication, detaching will break it.

To avoid any problems detaching, make sure you tick the "Close open connections" before clicking ok, that way any open connections to the db will be dropped.

share|improve this answer
add comment

The risk free approach would be to backup your existing database, rename it (old db), restore from backup moving files to new locations etc. Remove old database once you are happy.

share|improve this answer
add comment

If you want a risk free approach you can backup the database, rename the old database, then restore the database using WITH MOVE to specify the new file locations

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.