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.

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

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)

, , ,
Previous Post
When to Use sort_in_tempdb for Rebuilding Indexes? – Interview Question of the Week #207
Next Post
Does NOLOCK Really Applies No Lock? – Interview Question of the Week #209

Related Posts

1 Comment. Leave new

  • Thanks, Pinal, for once again providing helpful info, particularly in this case to refresh my memory and provide scripts.

    I have a suggestion that may save someone some typing. I had some databases with more than 50 files that accidentally got installed on the C drive. After detaching, I right clicked on Databases and clicked Attach to bring up the wizard. After pointing it to the primary data file, as if to re-attach as is, I clicked the Script button to generate the script to attach. From there it was easy to do a copy and paste for the more appropriate file locations.

    Reply

Leave a Reply

Menu