How to Move Log File or MDF File in SQL Server? – Interview Question of the Week #208

Question: How to Move Log File or MDF File in SQL Server?

Answer: This is one of the most frequent tasks, I always end up doing in my Comprehensive Database Performance Health Check. During health check, I often end up on the situation where one of the drives is very busy and running out of necessary IOPS. In that scenario, it is important to identify the busy LDF or MDF file and move it to the different drive which is not that busy. This is very critical and also helps to improve the performance of the system multifold.

Let us see how we can move SQL Server MDF file (data file) or LDF file (log file) to a different drive.

In this example, we will see how we can move the log file from one drive to another drive. You can use the same method to move the data file from one drive to another drive.

Step 0: Create a sample database

USE master
GO
CREATE DATABASE SampleDatabase
GO

Step 1: Capture Database Details

USE SampleDatabase
GO
sp_helpfile
GO

In the resultset remember the location of the data file and log file. The location will be under the column name filename.

Step 2: Detach Database

To detach the database you will need to move it first in a single user mode.

USE master
GO
-- Database in Single User Mode
ALTER DATABASE SampleDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
-- Detach Database
sp_detach_db 'SampleDatabase'
GO

Step 3: Move LDF (or MDF) File Manually

Move your log file or data file manually using Windows Explorer browser from one location to another location. Once the move is complete the next action would be to attach the database again with the new location.

Step 4: Attach the Database

USE master
GO
sp_attach_DB 'SampleDatabase',
'D:\Data\SampleDatabase_Data.mdf',
'E:\Log\SampleDatabase_Log.ldf'
GO

Once you run the above script, your database will come online. That’s it. It is a very simple procedure to do with the help of T-SQL.

Reference: Pinal Dave (https://blog.sqlauthority.com)