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?
Asked
Active
Viewed 2,982 times
4 Answers
4
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.
jl.
- 1,076
- 8
- 10
-
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
-
1Can 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
1
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.
DanBig
- 11,423
- 1
- 29
- 53
1
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.
Mark Broadbent
- 399
- 1
- 4
1
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
Gethyn Ellis
- 21
- 1