SQL Server 2Kx: How to move a database to a different partition in the local file system?

Question

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?

Solution 1

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

Solution 2

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!

Solution 3

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.

Solution 4

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.

View more solutions

Comments

[Paul Randal]

The previous answerer re-edited to add some of that info in - I guess you can choose the MSDN article or the KB article.

[Nick Kavadias]

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!

[MrGigu]

+1 for suggesting changing the defaults