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.

SQL Server creates its databases in the system program files directory structure.

I want to separate the database files away from the system partition.

Assuming the database is already created and populated: How do I move all files related to a database to a different location on a separate partition?

share|improve this question

4 Answers 4

Assuming it's a user database, detach the database, move the .mdf / .ldf files to the new location and reattach the database. You might need to browse to the new location of the .ldf file when you choose your .mdf file in the reattach screen.

To detach a database, right-click on it in management studio and choose Tasks | Detach.

To reattach, right-click on Databases in management studio and choose Attach..., then browse to the new location of your .mdf and .ldf files.

For system databases it's a little more involved, especially the master db. Have a look at this MSDN article that gives you the steps. Pay attention to the info regarding the resources system db for SQL2005+.

share|improve this answer

Checkout KB 224071 which explains how to move user and system databases - How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Hope this helps!

share|improve this answer
    
The previous answerer re-edited to add some of that info in - I guess you can choose the MSDN article or the KB article. –  Paul Randal Jun 11 '09 at 18:37
1  
A catch for young players is to make sure you either log in as sa when you dettach/attach or run exec sp_setuser 'dbo' before sp_attach_db Otherwise you're database aren't owned by DBO anymore, which can cause problems –  Nick Kavadias Jun 12 '09 at 9:57

Just to add a special note for SQL Server 2008. According to Microsoft, you can move database files without having to detach the database.

http://msdn.microsoft.com/en-us/library/ms345483.aspx

It basically amounts to taking the database offline and then executing ALTER statements that modify the filename mappings to the database/log files.

share|improve this answer
1  
You still have to manually move the files across, which can be a pain if they're big. It should do it for you dammit! –  Nick Kavadias Jun 12 '09 at 9:52

I would strongly recommend that you also change the default location for 'Data' and 'Log'. I typically do this as part of the SQL Server installer putting the logs on to one RAID array and the data on to a separate one.

Anyway, after the event in Management Studio, right click the server name->Properties->Database Settings. Change "Database default locations" off of your system drive.

share|improve this answer
1  
+1 for suggesting changing the defaults –  Mark Henderson Jun 17 '09 at 2:38

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.