6

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?

4 Answers 4

17

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+.

3

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!

2
  • The previous answerer re-edited to add some of that info in - I guess you can choose the MSDN article or the KB article. Jun 11, 2009 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 Jun 12, 2009 at 9:57
2

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.

1
  • 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! Jun 12, 2009 at 9:52
2

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.

1
  • 1
    +1 for suggesting changing the defaults Jun 17, 2009 at 2:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .