A lot of times we come across issues when our disk space is consumed and we need to move our database MDF & LDF files from their default location.

In an earlier version of SQL Server, you have to use Detach and Attach method in order to move database files location. However, it is an old method and you do not have to follow the same method now.

I use the following steps to move database MDF and LDF files from one location to another.

Let’s assume the current location of MDF and LDF files are in the C drive and you want to move these files to the D drive.

Open SQL Management Studio and open the query window and follow the below steps. replace “YOURDATABASENAME ” with the name of the actual database.

Get Current Location

use the following SQL to get the current location of database files

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'YOURDATABASENAME ');

Offline Database

ALTER DATABASE YOURDATABASENAME SET OFFLINE;

Move MDF & LDF files

Since the database is now offline, you can move MDF and LDF files to a new location with the xCopy command in the command prompt or manually copy/paste using explorer.

Update File location in the database

You can use the following command to update MDF and LDF file location in the database

ALTER DATABASE YOURDATABASENAME 
MODIFY FILE ( NAME = YOURDATABASENAME_Data, FILENAME = 'D:\data\YOURDATABASENAME.mdf' );
ALTER DATABASE YOURDATABASENAME 
MODIFY FILE ( NAME = YOURDATABASENAME_Logs, FILENAME = 'D:\data\YOURDATABASENAME.ldf' );
GO

Online Database

ALTER DATABASE YOURDATABASENAMESET ONLINE

Verify

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'YOURDATABASENAME ');

Categorized in:

Sql

Tagged in:

, ,