How to move a SQL Server database in SQL 2005

I work with several customers who have SQL databases, the applications that use these often have their own support providers and sometimes I find they leave behind a bit of a mess when they do maintenance.  Often I have 5% disk space left on my database disks when they have completed a year end or stock take and left copies of databases.

So I thought it was time I moved databases to where I want them and not have to rely on someone else.

It is actually quite a simple process to move a database and can all be done via the SQL Query in the Studio Manager application.

The first thing to do though is back up your database and the "master" database, this is really important as the master database contains the information about the configuration of SQL Server so if you lose this, you lose the lot.

Next you need to know the database file location and log file location of the database you want to move.  You can find this out by running this query

use "mydatabase"
go
sp_helpfile
go


This will display the database and log file locations for you.

Next we will detach the database.

use master
go
sp_detach_db "mydatabase"
go


The database is now detached from the SQL Server Engine so we can move the files to their new locations.

Now we reattach the database and log files.

use master
go
sp_attach_db "mydatabase" , "E:\databasefiles\mydatabase.mdf" , "F:\databaselogs\mydatabaselogs.ldf"
go


The database will now be reattached to the locations specified.  You can confirm this with the location Query from the first step again.

More information on this topic is found here.