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.
- SQL SERVER – Rename Logical Database File Name for Any Database
- SQL SERVER – Rename Physical Database File Name for Any Database
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');
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.
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.
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.
Reference: Pinal Dave (https://blog.SQLAuthority.com)