How to Move SQL Server MDF and LDF Files?

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

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.

1 2 3

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

Step 2: Take Database Offline

You can take the database offline running following command.

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

Step 4: Change the file location inside SQL Server

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

1 2 3 4 5

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.

1	ALTER DATABASE SQLAuthority SET ONLINE;

Step 6: Check the database file location

You can verify your database move by running following command.

Well, that’s it. You do not have use older method of Detach or Attach anymore if you want to move your database.