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)