Moving SQL Server database files to a new drive: user databases, tempdb and the default paths

#When you need this

The data drive is nearly full, the storage team has presented a faster volume, or someone installed SQL Server with everything on C:. You need to move one or more databases' .mdf, .ndf and .ldf files to a new path with as little downtime as possible.

The supported method is short: tell SQL Server the new path, take the database offline, copy the files, bring it online. The parts that go wrong are file permissions, typos in logical names and databases that belong to an Availability Group. This runbook covers those.

Warning

Each user database is unavailable from the moment it goes offline until it comes back online. The outage is roughly the time it takes to copy its files. Schedule a window and tell the application owners.

#Step 1: Inventory the files

Get the logical name, current path and size of every file you plan to move:

SELECT DB_NAME(mf.database_id)      AS database_name,
       mf.name                      AS logical_name,
       mf.type_desc,
       mf.physical_name,
       CAST(mf.size * 8 / 1024.0 AS decimal(12,1)) AS size_mb,
       d.state_desc
FROM sys.master_files AS mf
JOIN sys.databases    AS d ON d.database_id = mf.database_id
WHERE mf.physical_name LIKE N'D:\%'      -- the drive you are emptying
ORDER BY database_name, mf.file_id;

Check three things before going further:

• Free space on the target. The new volume needs the full size of the files (not the used space; the files copy at their allocated size).
• Availability Groups, mirroring, log shipping. SELECT name, replica_id FROM sys.databases WHERE replica_id IS NOT NULL; lists AG databases. They can't simply be set offline; see the AG section below.
• A recent backup. Take a full backup (or confirm last night's) of every database you move.

#Step 2: Prepare the target folder and permissions

Create the folders and give the SQL Server service account full control. Most "the database won't come back online" incidents are this step.

Find the service account:

SELECT servicename, service_account
FROM sys.dm_server_services;

Then grant it rights on the new folders (run PowerShell as administrator; replace the account with your output, for example NT SERVICE\MSSQLSERVER for a default instance or NT SERVICE\MSSQL$SALES for a named one):

New-Item -ItemType Directory -Path 'E:\SQLData','F:\SQLLogs' -Force
icacls 'E:\SQLData' /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'
icacls 'F:\SQLLogs' /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'

#Step 3: Point SQL Server at the new paths

MODIFY FILE only updates metadata in master. Nothing moves yet, and the change takes effect the next time the database starts. Run one statement per file, using the logical names from Step 1:

ALTER DATABASE SalesDB
    MODIFY FILE (NAME = N'SalesDB',     FILENAME = N'E:\SQLData\SalesDB.mdf');
ALTER DATABASE SalesDB
    MODIFY FILE (NAME = N'SalesDB_log', FILENAME = N'F:\SQLLogs\SalesDB_log.ldf');

SQL Server replies with "The file ... has been modified in the system catalog. The new path will be used the next time the database is started." Re-run the Step 1 query: physical_name now shows the new path even though the files haven't moved.

Caution

Double-check every path. If you mistype one, the database fails to start after Step 4. You can still recover: run MODIFY FILE again with the correct path, then set the database online. But that adds to the outage.

#Step 4: Take the database offline and copy the files

ALTER DATABASE SalesDB SET OFFLINE WITH ROLLBACK IMMEDIATE;

WITH ROLLBACK IMMEDIATE kills open sessions and rolls back their transactions. Without it, the statement waits until all users disconnect.

Copy the files. Prefer copy over move: the originals are your rollback.

Copy-Item 'D:\Data\SalesDB.mdf'     'E:\SQLData\SalesDB.mdf'
Copy-Item 'D:\Logs\SalesDB_log.ldf' 'F:\SQLLogs\SalesDB_log.ldf'

For large files, robocopy restarts cleanly after network blips and reports throughput:

robocopy D:\Data E:\SQLData SalesDB.mdf /J /R:2 /W:5

/J uses unbuffered I/O, which is faster for very large files.

#Step 5: Bring it online and verify

ALTER DATABASE SalesDB SET ONLINE;

SELECT name, state_desc FROM sys.databases WHERE name = N'SalesDB';
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SalesDB');

The state should be ONLINE. Run a quick DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS; on smaller databases, or at least a test query from the application. Delete the old files only after the application team confirms everything works. Keep them for a day if you have the space.

#Errors when it won't come online

The SQL Server error log shows the exact file and OS error:

EXEC sys.xp_readerrorlog 0, 1, N'SalesDB';

#Moving tempdb

You can't take tempdb offline. Change the paths, then restart the service. SQL Server creates new tempdb files at startup.

SELECT name, physical_name FROM sys.master_files WHERE database_id = 2;

ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILENAME = N'T:\TempDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = N'templog', FILENAME = N'T:\TempDB\templog.ldf');
-- repeat for every extra data file (logical names such as temp2, temp3 vary by install)

Restart the service (this restarts SQL Server Agent too):

Restart-Service -Name 'MSSQLSERVER' -Force

Then delete the old tempdb files by hand. SQL Server doesn't remove them.

Warning

If the service account can't write to the new tempdb folder, SQL Server won't start. Grant permissions first. If it fails anyway, start the instance with -f (minimal configuration) from an administrator prompt and point tempdb back.

#Availability Group databases

You can't set an AG database offline on the primary. A pattern that avoids removing it from the AG:

1. On a secondary replica, run the MODIFY FILE statements for that replica's copy of the database.
2. Stop the SQL Server service on that secondary, copy the files, start the service. The AG resynchronises.
3. Fail over so a moved replica becomes primary, then repeat on the old primary.

Test this in a non-production AG first. If your version rejects MODIFY FILE on the secondary, remove the database from the AG on that replica, move it, then rejoin it. Read the AG not-synchronizing runbook before you start.

#Doing it with dbatools

Move-DbaDbFile wraps the offline, copy, modify and online steps:

Move-DbaDbFile -SqlInstance SQL01 -Database SalesDB -FileType Data -FileDestination 'E:\SQLData'
Move-DbaDbFile -SqlInstance SQL01 -Database SalesDB -FileType Log  -FileDestination 'F:\SQLLogs'

The parameters have changed between dbatools releases. Check Get-Help Move-DbaDbFile -Full in your installed version before scripting it, especially before using -DeleteAfterMove.

#Prevent it next time

• Set the instance default data and log paths (SSMS > Server Properties > Database Settings) so new databases don't land on C:. Check them with SELECT SERVERPROPERTY('InstanceDefaultDataPath'), SERVERPROPERTY('InstanceDefaultLogPath');.
• Alert on volume free space, not just database size, so you can move files in a planned window instead of during an incident.
• Keep data, log, tempdb and backups on separate volumes from the start, and exclude those folders from file-level antivirus scanning.