Sql Server change data and log path of existing database

Question

I am having a SQL Server 2008 installation with almost 15 databases running on it. Now due to scarcity of space I would like to move the data path to another drive. What is the best practice for this. Please explain in details if including any SQL commands as I'm relatively new to SQL Server administration.

Note - I have already changed the path in SQL server properties from SQL Management Studio 2008, to the new path. But I would also like the existing databases to reside in the new path.

Answer 1

First, detach database:

USE master;
GO
-- Important! We need to drop the existing connections.
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db @dbname = N'DBName';
GO

Next step - copy files .mdf and .ldf of this database files to new location

And then attaching the database:

USE master;
EXEC sp_attach_db @dbname = N'dbName', 
@filename1 = N'',  --path do .mdf
@filename2 = N'';  --path to .ldf
GO

If you don't want to attach and detach all databases one-by-one, you can generate SQL script to attach and detach all databases you need (execept system, of course), using curosr that searches in sys.databases dynamic management view. But don't forget to copy the database files.

Answer 2

One way is to detach and attach.

As for commands/steps, see the MSDN article "How to: Move a Database Using Detach and Attach (Transact-SQL)"