How to Move SQL Server MDF and LDF Files? – Interview Question of the Week #189

September 2, 2018

Question: How to Move SQL Server MDF and LDF Files?

Answer: I recently received this question during my Comprehensive Database Performance Health Check. If you read the older blogs or search on the internet, you will find a solution where people are talking about Detach and Attach method. However, it is an old method and it is not necessary to follow the same method now.

I personally use the following method to move the data and log files for my customers. Let us learn that step by step.

Before continuing to this blog post, you should read the following two blog posts as they will be useful to get logical and physical file names.

Let us assume that we want to move database SQLAuthority from their D drive to E drive on my machine.

Step 1: Original Location

First, we will get the location of the physical files using the following command.

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

How to Move SQL Server MDF and LDF Files? - Interview Question of the Week #189 physicalmove1

Step 2: Take Database Offline

You can take the database offline running following command.

ALTER DATABASE SQLAuthority SET OFFLINE;

Step 3: Move Files – MDF and LDF

Now in this step, you will have to move your database files from the original location to the new location.

How to Move SQL Server MDF and LDF Files? - Interview Question of the Week #189 physicalmove2

Step 4: Change the file location inside SQL Server

Running following command will change the location of the file for SQL Server.

ALTER DATABASE SQLAuthority
MODIFY FILE ( NAME = SQLAuthority_Data, FILENAME = 'E:\data\SQLAuthority.mdf' );
ALTER DATABASE SQLAuthority
MODIFY FILE ( NAME = SQLAuthority_Logs, FILENAME = 'E:\data\SQLAuthority_Logs.ldf' );
GO

Step 5: Bring Database Online

Now run following command to bring the database online.