SQL Server Move Database Files (MDF and LDF) to Another Drive

My 16 MB test database was offline for about 2 seconds. The SQL Server move database files routine Microsoft recommends: ALTER DATABASE ... MODIFY FILE, SET OFFLINE, copy the MDF and LDF, SET ONLINE.

Here’s the end state:

Before You Start: Test Setup and Sample Database

Test setup: SQL Server 2025 (17.0.1000.7) Standard Developer, default instance, sqlcmd and PowerShell 7 in VS Code (check your version). Take a full backup first.

The database movedbfiles_demo starts in C:\DatabaseFAQsDemo\SQLMove\OldData with one table:

CREATE TABLE dbo.Orders (
    OrderID  int          PRIMARY KEY,
    Customer nvarchar(20) NOT NULL,
    Amount   decimal(8,2) NOT NULL
);
INSERT INTO dbo.Orders VALUES
(1, N'Hannah Brooks', 129.99), (2, N'Tyler Reed', 54.10),
(3, N'Grace Miller', 310.00), (4, N'Marcus Bennett', 22.75),
(5, N'Olivia Hayes', 88.40);

Step 1: Find the Current File Paths in sys.master_files

Later steps need the logical names. Not sure of the database name? List the database names.

SET NOCOUNT ON;
SELECT CAST(name AS varchar(9))           AS LogicalName,
       CAST(physical_name AS varchar(49)) AS PhysicalName
FROM sys.master_files
WHERE database_id = DB_ID(N'movedbfiles_demo');

Result:

LogicalName PhysicalName                                     
----------- -------------------------------------------------
SalesData   C:\DatabaseFAQsDemo\SQLMove\OldData\SalesData.mdf
SalesLog    C:\DatabaseFAQsDemo\SQLMove\OldData\SalesLog.ldf

SSMS shows the same paths under Properties > Files.

Step 2: Grant the SQL Server Service Account Access to the New Folder

The engine runs as NT SERVICE\MSSQLSERVER. This Windows grant is separate from SQL Server user permissions.

icacls 'C:\DatabaseFAQsDemo\SQLMove\NewData' /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'

icacls says:

processed file: C:\DatabaseFAQsDemo\SQLMove\NewData
Successfully processed 1 files; Failed processing 0 files

Checkpoint: 1 processed, 0 failed.

Step 3: Run ALTER DATABASE MODIFY FILE, Then SET OFFLINE

One MODIFY FILE per file; NAME is the logical name from CREATE DATABASE.

ALTER DATABASE movedbfiles_demo
    MODIFY FILE (NAME = SalesData,
    FILENAME = 'C:\DatabaseFAQsDemo\SQLMove\NewData\SalesData.mdf');
ALTER DATABASE movedbfiles_demo
    MODIFY FILE (NAME = SalesLog,
    FILENAME = 'C:\DatabaseFAQsDemo\SQLMove\NewData\SalesLog.ldf');
ALTER DATABASE movedbfiles_demo SET OFFLINE WITH ROLLBACK IMMEDIATE;

Output:

The file "SalesData" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "SalesLog" has been modified in the system catalog. The new path will be used the next time the database is started.

Checkpoint: two “modified in the system catalog” lines.

Step 4: Copy the MDF and LDF Files

$old = 'C:\DatabaseFAQsDemo\SQLMove\OldData'
$new = 'C:\DatabaseFAQsDemo\SQLMove\NewData'
Copy-Item "$old\SalesData.mdf", "$old\SalesLog.ldf" -Destination $new
Get-ChildItem $new | Select-Object Name, Length

Copied:

Name           Length
----           ------
SalesData.mdf 8388608
SalesLog.ldf  8388608

Important: copy, don’t move; the old files are your rollback. My first try hit “Access to the path is denied” because SQL Server locks its files to the service account and Administrators. Open PowerShell as administrator.

Test the Move: SET ONLINE and Verify

SET NOCOUNT ON;
ALTER DATABASE movedbfiles_demo SET ONLINE;
SELECT CAST(name AS varchar(9))           AS LogicalName,
       CAST(physical_name AS varchar(49)) AS PhysicalName,
       CAST(state_desc AS varchar(7))     AS State
FROM sys.master_files
WHERE database_id = DB_ID(N'movedbfiles_demo');
SELECT COUNT(*) AS Orders FROM movedbfiles_demo.dbo.Orders;

What came back:

LogicalName PhysicalName                                      State  
----------- ------------------------------------------------- -------
SalesData   C:\DatabaseFAQsDemo\SQLMove\NewData\SalesData.mdf ONLINE 
SalesLog    C:\DatabaseFAQsDemo\SQLMove\NewData\SalesLog.ldf  ONLINE 
Orders     
-----------
          5

Both paths say NewData and the count is 5. Run a database size check if the new disk is smaller, then Remove-Item the old copies.

Troubleshooting: Why Does SET ONLINE Fail With Msg 5120?

I skipped Step 2:

SET NOCOUNT ON;
ALTER DATABASE movedbfiles_demo SET ONLINE;
SELECT CAST(name AS varchar(9))           AS LogicalName,
       CAST(physical_name AS varchar(49)) AS PhysicalName,
       CAST(state_desc AS varchar(7))     AS State
FROM sys.master_files
WHERE database_id = DB_ID(N'movedbfiles_demo');
SELECT COUNT(*) AS Orders FROM movedbfiles_demo.dbo.Orders;

Error (trimmed):

Msg 5120, Level 16, State 101, Server YOUR-SERVER, Line 2
Unable to open the physical file "C:\DatabaseFAQsDemo\SQLMove\NewData\SalesData.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Server YOUR-SERVER, Line 2
Unable to open the physical file "C:\DatabaseFAQsDemo\SQLMove\NewData\SalesLog.ldf". Operating system error 5: "5(Access is denied.)".
Msg 5181, Level 16, State 5, Server YOUR-SERVER, Line 2
Could not restart database "movedbfiles_demo". Reverting to the previous status.
Msg 5069, Level 16, State 1, Server YOUR-SERVER, Line 2
ALTER DATABASE statement failed.
...

Authenticated Users already had Modify on the folder. Didn’t help. Grant the service account and rerun; a missing file gives error 2 instead of 5.

How to Move Only the Log File to Another Drive

$db  = 'movedbfiles_demo'
$log = 'C:\DatabaseFAQsDemo\SQLMove\NewLogs'
icacls $log /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F' | Out-Null
sqlcmd -S localhost -E -C -Q "ALTER DATABASE $db MODIFY FILE
  (NAME = SalesLog, FILENAME = '$log\SalesLog.ldf');
  ALTER DATABASE $db SET OFFLINE WITH ROLLBACK IMMEDIATE;"
Move-Item 'C:\DatabaseFAQsDemo\SQLMove\OldData\SalesLog.ldf' $log
sqlcmd -S localhost -E -C -Q "SET NOCOUNT ON; ALTER DATABASE $db SET ONLINE;
  SELECT CAST(physical_name AS varchar(49)) AS PhysicalName
  FROM sys.master_files WHERE database_id = DB_ID('$db');"

Output:

The file "SalesLog" has been modified in the system catalog. The new path will be used the next time the database is started.
PhysicalName                                     
-------------------------------------------------
C:\DatabaseFAQsDemo\SQLMove\OldData\SalesData.mdf
C:\DatabaseFAQsDemo\SQLMove\NewLogs\SalesLog.ldf

The data file stayed put. To keep new databases off the old drive, change the default locations in server properties.

Detach and Attach With sp_detach_db and FOR ATTACH

Microsoft recommends MODIFY FILE over this; a detached database is gone until the attach works.

$db  = 'movedbfiles_demo'
$old = 'C:\DatabaseFAQsDemo\SQLMove\OldData'
$new = 'C:\DatabaseFAQsDemo\SQLMove\NewData'
sqlcmd -S localhost -E -C -Q "EXEC sp_detach_db @dbname = N'$db';"
Move-Item "$old\SalesData.mdf", "$old\SalesLog.ldf" -Destination $new
sqlcmd -S localhost -E -C -Q "CREATE DATABASE $db
  ON (FILENAME = '$new\SalesData.mdf'), (FILENAME = '$new\SalesLog.ldf')
  FOR ATTACH;"
sqlcmd -S localhost -E -C -Q "SET NOCOUNT ON; SELECT CAST(state_desc
  AS varchar(7)) AS State FROM sys.databases WHERE name = '$db';"

Final state:

State  
-------
ONLINE

After the detach only my account had rights on the files, so Move-Item worked unelevated. For another server, use backup and restore.

What About tempdb and the System Databases?

I didn’t move these (shared instance). Per Microsoft Learn, tempdb needs MODIFY FILE and a restart, no copy; model and msdb: MODIFY FILE, stop, copy, start; master uses the -d and -l startup parameters.

My Takeaways on Moving Database Files, and Where to Take It Next

1. Grant the service account first.
2. Copy, verify, delete.
3. sys.master_files said ONLINE while my database was offline. Check sys.databases.

• Back up to Azure Blob Storage
• MongoDB data directory errors

Frequently Asked Questions