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

Question: How do you move a SQL Server data or log file to another drive?

Answer: For planned relocation within the same instance, change the file’s recorded path with ALTER DATABASE, take the database offline, move the physical file, then bring the database online. Detach and attach is another procedure, but it is usually unnecessary for this task.

This is a frequent discussion in my performance health checks. A busy storage path can make a data or log file a bottleneck. Moving it can help when the destination actually provides better capacity or throughput. A different drive letter alone does not prove that it uses different physical storage.

First record the files and prepare the outage

Keep a verified backup and record every logical name and physical path. The names below are examples, not names to assume on your instance. The new folder must exist on the SQL Server machine, with the required access for its Database Engine service account. Coordinate application connections and leave enough destination space.

-- Documentary example. Review names, paths, backup and outage first.
USE master;
SELECT name, type_desc, physical_name
FROM sys.master_files WHERE database_id = DB_ID(N'SampleDatabase');

-- Replace this logical name with the catalog result above.
ALTER DATABASE SampleDatabase MODIFY FILE
    (NAME = N'SampleDatabase_log', FILENAME = N'E:\Log\SampleDatabase_Log.ldf');
ALTER DATABASE SampleDatabase SET OFFLINE;

-- While OFFLINE, physically move the existing log to that server path.
-- No SQL statement above copies or moves the physical file.
ALTER DATABASE SampleDatabase SET ONLINE;
SELECT name, type_desc, physical_name
FROM sys.master_files WHERE database_id = DB_ID(N'SampleDatabase');

OFFLINE needs exclusive access. Let work finish and close connections in the planned outage. If you deliberately use WITH ROLLBACK IMMEDIATE, it disconnects users and rolls back their work; it is a separate operational decision. The metadata update does not move the file, so complete the physical move while the database is offline.

The original detach/attach approach

The original walkthrough created a disposable SampleDatabase, used sp_helpfile to record its files, detached it, moved the log in Windows Explorer, and attached the files at their new paths. That sequence still explains the process. The attach command should now use CREATE DATABASE … FOR ATTACH rather than the deprecated sp_attach_db.

-- Alternative detach/attach procedure, not a continuation of the offline example.
USE master;
-- Close application connections before entering single-user mode.
ALTER DATABASE SampleDatabase SET SINGLE_USER;
EXEC sys.sp_detach_db @dbname = N'SampleDatabase';

-- Move the detached files on the server, keeping every data and log file.
-- Include additional files if this database has them.
CREATE DATABASE SampleDatabase ON
    (FILENAME = N'D:\Data\SampleDatabase_Data.mdf'),
    (FILENAME = N'E:\Log\SampleDatabase_Log.ldf')
FOR ATTACH;
ALTER DATABASE SampleDatabase SET MULTI_USER;

Use one procedure or the other. Adjust both listed paths to the actual files, and include any additional data files. Keep the existing log; moving it is not a reason to discard or rebuild it. After either procedure, verify the database is ONLINE, its files point to the intended server locations, and normal application access works. Attach does not turn this operation into a backup.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.