Step-by-step procedures to relocate master, model, msdb, tempdb, and user database files from default C: installation paths to dedicated data and log volumes, with a single-restart strategy for system databases.

SQL Server: Moving System and User Database Files Off the C Drive to Dedicated Volumes

Diagram showing SQL Server database files migrating from C drive to dedicated D and L volumes

A default SQL Server installation places every database file — master, model, msdb, tempdb, and every user database — under C:\Program Files\Microsoft SQL Server\MSSQL{version}.{instance}\MSSQL\DATA\. The C: drive is the OS volume. Over time it fills. Backup agents, Windows updates, crash dumps, and growing user databases all compete for the same spindle or partition. On the day C: hits 95%, the options are either an emergency cleanup or a file relocation that should have been done at provisioning time.

The relocation is straightforward but not uniform. Each database type has a different mechanism, and master requires a procedure entirely separate from every other database. Doing them in the wrong order — or physically moving files before updating SQL Server’s metadata — leaves the instance unable to start.

The answer

User databases go offline, their file paths are updated in sys.master_files via ALTER DATABASE MODIFY FILE, the physical files are moved, and the databases are brought back online — one at a time, with no instance restart required. System databases require an instance restart: model, msdb, and tempdb paths are updated with ALTER DATABASE MODIFY FILE, physical files for model and msdb are moved during the restart window, and tempdb is recreated automatically. master is handled last and separately using startup parameters in SQL Server Configuration Manager, not ALTER DATABASE.

Batching all system database changes into a single planned restart minimises disruption.

What you will learn

How sys.master_files maps every database file and how to audit current paths before touching anything
Why each system database requires a different relocation mechanism
How to combine tempdb, model, msdb, and master changes into a single planned restart
How to update the instance-level default paths so future databases are not created back on C:
How to verify no database files remain on the wrong volume after the migration

Scope: SQL Server 2016, 2017, 2019, and 2022, on Windows, for default and named instances. Applies to on-premises hardware and SQL Server on Azure VMs. Does not apply to Azure SQL Database or Azure SQL Managed Instance — those services do not expose file-level storage management. Procedures require sysadmin server role and local Windows Administrator access to the host.

Impact: User database relocations take each individual database offline for the duration of the file move. System database relocations require a full SQL Server instance restart. Plan a maintenance window. Take a full backup before starting.

Before you start

Confirm the following before making any changes:

Full backups of all databases have completed successfully and have been verified.
Target directories exist on the destination volumes and the SQL Server service account has read/write access to them.
You have sysadmin membership on the instance.
You have local Windows Administrator access to the host (required for the master move).
A maintenance window is approved for the system database restart.
SQL Server Configuration Manager is installed on the host (installed with SQL Server by default).

Identify the SQL Server service account before creating directories:

SELECT service_account
FROM   sys.dm_server_services
WHERE  servicename LIKE 'SQL Server (%';

Grant the service account full control on each target directory. SQL Server does not grant its own permissions.

Audit your current file locations

Before moving anything, capture the current state of every database file on the instance:

SELECT
    DB_NAME(database_id)  AS database_name,
    database_id,
    type_desc,
    name                  AS logical_name,
    physical_name,
    state_desc,
    size * 8 / 1024       AS size_mb
FROM sys.master_files
ORDER BY database_id, type;

Identify every file still on C:

SELECT
    DB_NAME(database_id) AS database_name,
    type_desc,
    logical_name         = name,
    physical_name
FROM sys.master_files
WHERE physical_name LIKE 'C:%'
ORDER BY database_id, type;

Save both result sets. You will use them to confirm the migration is complete.

Overview: one mechanism per database type

Database	Mechanism	Instance restart required	Physical file move required
User databases	`ALTER DATABASE SET OFFLINE` → `MODIFY FILE` → move files → `SET ONLINE`	No	Yes
`tempdb`	`ALTER DATABASE MODIFY FILE`	Yes (tempdb is recreated)	No
`model`	`ALTER DATABASE MODIFY FILE`	Yes	Yes
`msdb`	`ALTER DATABASE MODIFY FILE`	Yes	Yes
`master`	Startup parameters in SQL Server Configuration Manager	Yes	Yes

The recommended execution order is: user databases first (no restart, minimise disruption per database), then system databases in a single planned restart window.

Step 1: Move user databases

Each user database can be relocated independently with no instance restart. Take each database offline, update the file path metadata, move the physical files, and bring it back online.

Identify the logical file names for the target database:

SELECT name AS logical_name, physical_name, type_desc
FROM   sys.master_files
WHERE  database_id = DB_ID(N'YourDatabase');

Take the database offline, update paths, move files, and bring it back:

-- Take offline immediately; adjust ROLLBACK IMMEDIATE to ROLLBACK AFTER N SECONDS
-- if sessions need time to commit work
ALTER DATABASE [YourDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [YourDatabase] MODIFY FILE (
    NAME     = N'YourDatabase',
    FILENAME = N'D:\SQLData\YourDatabase.mdf'
);
GO

ALTER DATABASE [YourDatabase] MODIFY FILE (
    NAME     = N'YourDatabase_log',
    FILENAME = N'L:\SQLLog\YourDatabase_log.ldf'
);
GO

Move the physical files while the database is offline:

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\YourDatabase.mdf" `
          -Destination "D:\SQLData\YourDatabase.mdf"

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\YourDatabase_log.ldf" `
          -Destination "L:\SQLLog\YourDatabase_log.ldf"

Bring the database back online:

ALTER DATABASE [YourDatabase] SET ONLINE;
GO

-- Confirm
SELECT name, state_desc FROM sys.databases WHERE name = N'YourDatabase';

Generating statements for multiple user databases

On instances with many databases, generate the ALTER DATABASE statements from the catalog rather than writing them by hand:

SELECT
    'ALTER DATABASE [' + DB_NAME(database_id) + '] MODIFY FILE (NAME = N''' + name + ''', FILENAME = N''' +
    CASE type
        WHEN 0 THEN 'D:\SQLData\'
        WHEN 1 THEN 'L:\SQLLog\'
        ELSE    'D:\SQLData\'   -- secondary data files (.ndf)
    END +
    REVERSE(LEFT(REVERSE(physical_name), CHARINDEX('\', REVERSE(physical_name)) - 1)) +
    ''');' AS alter_statement
FROM  sys.master_files
WHERE database_id > 4          -- skip system databases
  AND state_desc = 'ONLINE'
ORDER BY database_id, type;

Review and adjust the target paths before executing. The query assumes a simple two-volume layout; adjust the CASE expression for your volume structure. Secondary data files (.ndf) default to the data volume; change as appropriate.

Step 2: Prepare system database file changes

The following commands update the file path metadata in sys.master_files but do not move any physical files. Execute all of them before restarting the instance. They take effect at the next startup.

tempdb

tempdb is dropped and recreated from scratch at every startup. There are no physical files to move — SQL Server creates new files at whatever path is registered. Update the paths and let the restart handle the rest.

-- Primary data file
ALTER DATABASE tempdb MODIFY FILE (
    NAME     = N'tempdev',
    FILENAME = N'D:\SQLData\tempdb.mdf'
);
GO

-- Log file
ALTER DATABASE tempdb MODIFY FILE (
    NAME     = N'templog',
    FILENAME = N'L:\SQLLog\templog.ldf'
);
GO

If additional tempdb data files exist (tempdev2, tempdev3, and so on), update each:

-- List all tempdb files first
SELECT name, physical_name FROM sys.master_files WHERE database_id = 2;
GO

-- Update each additional file
ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev2', FILENAME = N'D:\SQLData\tempdb2.ndf');
GO

After the restart, the old tempdb files on C: become orphaned. SQL Server does not delete them. Remove them manually once you have confirmed the instance started successfully.

model

ALTER DATABASE model MODIFY FILE (
    NAME     = N'modeldev',
    FILENAME = N'D:\SQLData\model.mdf'
);
GO

ALTER DATABASE model MODIFY FILE (
    NAME     = N'modellog',
    FILENAME = N'L:\SQLLog\modellog.ldf'
);
GO

msdb

ALTER DATABASE msdb MODIFY FILE (
    NAME     = N'MSDBData',
    FILENAME = N'D:\SQLData\MSDBData.mdf'
);
GO

ALTER DATABASE msdb MODIFY FILE (
    NAME     = N'MSDBLog',
    FILENAME = N'L:\SQLLog\MSDBLog.ldf'
);
GO

Verify the metadata has been updated before proceeding:

SELECT DB_NAME(database_id) AS db, name, physical_name
FROM   sys.master_files
WHERE  database_id IN (DB_ID('model'), DB_ID('msdb'), 2)
ORDER BY database_id, type;

Confirm the physical_name values reflect the new paths. The files have not moved yet.

Step 3: Prepare master startup parameters

master stores its own file paths in startup parameters, not in sys.master_files in a way accessible through ALTER DATABASE. The paths are read before the database engine loads, so they must be changed through SQL Server Configuration Manager.

Open SQL Server Configuration Manager as a local Administrator.

Navigate to SQL Server Services → right-click the SQL Server engine service → Properties → Startup Parameters tab.

The existing parameters include entries similar to:

-dC:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf
-eC:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG
-lC:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Select the -d entry and change the value to the new master data file path:

-dD:\SQLData\master.mdf

Select the -l entry and change the value to the new master log file path:

-lL:\SQLLog\mastlog.ldf

Click Update, then Apply.

Do not remove or modify the -e parameter unless you are also relocating the SQL Server error log. Leave all other startup parameters untouched.

Critical: The startup parameters must be updated before stopping the service. If you move the master files first and then change the parameters, the instance will fail to start and you will need to change the parameters with the service stopped — which requires editing the registry directly.

Step 4: Stop the instance and move system database files

Stop SQL Server Agent and any other dependent services first:

Stop-Service -Name "SQLSERVERAGENT" -Force
Stop-Service -Name "MSSQLSERVER"    -Force

For named instances, replace MSSQLSERVER with MSSQL$InstanceName and SQLSERVERAGENT with SQLAgent$InstanceName.

Confirm the service has stopped before moving any files.

Move model:

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\model.mdf" `
          -Destination "D:\SQLData\model.mdf"

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\modellog.ldf" `
          -Destination "L:\SQLLog\modellog.ldf"

Move msdb:

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf" `
          -Destination "D:\SQLData\MSDBData.mdf"

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf" `
          -Destination "L:\SQLLog\MSDBLog.ldf"

Move master:

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf" `
          -Destination "D:\SQLData\master.mdf"

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\mastlog.ldf" `
          -Destination "L:\SQLLog\mastlog.ldf"

Do not move tempdb files. They will be created at the new location automatically.

Step 5: Start the instance and verify

Start the SQL Server service:

Start-Service -Name "MSSQLSERVER"

Monitor the SQL Server error log during startup. The log is written to the -e path configured in startup parameters. Confirm the instance reaches “SQL Server is now ready for client connections” before reconnecting.

Start dependent services:

Start-Service -Name "SQLSERVERAGENT"

Verify all database files are at the intended locations:

SELECT
    DB_NAME(database_id)  AS database_name,
    type_desc,
    name                  AS logical_name,
    physical_name,
    state_desc
FROM sys.master_files
ORDER BY database_id, type;

Confirm no files remain on C::

SELECT DB_NAME(database_id) AS database_name, name, physical_name
FROM   sys.master_files
WHERE  physical_name LIKE 'C:%';

An empty result set means the migration is complete.

Remove orphaned tempdb files from the old location on C: once the instance is confirmed healthy:

# Adjust the path to match your instance version
Remove-Item "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb.mdf"
Remove-Item "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\templog.ldf"

Step 6: Update the default paths for new databases

SQL Server remembers separate default paths for new database data files, log files, and backup files. These are set at installation time and point to C:. Unless changed, every subsequent CREATE DATABASE statement and SSMS “New Database” dialog will default back to C:.

Update the defaults via T-SQL:

-- Requires sysadmin; changes take effect immediately for new databases
EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData',
    REG_SZ,
    N'D:\SQLData';
GO

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog',
    REG_SZ,
    N'L:\SQLLog';
GO

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'BackupDirectory',
    REG_SZ,
    N'E:\SQLBackup';
GO

Alternatively, set these through SSMS: right-click the instance → Properties → Database Settings → Database default locations.

Verify the current defaults are applied:

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData';

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog';

EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'BackupDirectory';

In practice

This relocation is needed in several recurring situations:

New server provisioning: The instance was installed with default settings before storage volumes were attached and configured. This is the most common cause: storage provisioning and SQL Server installation happen at different times in most build processes.
C: drive capacity pressure: Monitoring alerts on disk space, or a failed SQL Server Agent job due to a full C:, forces an emergency relocation.
Compliance requirements: Audit findings that OS and database data must reside on separate volumes. Common in financial services and healthcare environments.
Performance separation: Isolating tempdb onto a dedicated high-speed local disk (NVMe or ephemeral storage on cloud VMs), data files onto a RAID-10 or SAN LUN, and log files onto a sequential-write-optimised volume.
Storage tiering: Moving archival databases to slower, cheaper storage while keeping active databases on faster volumes, using separate drive letters per tier.

On instances with many user databases, the bulk of the work is in the user database loop. Each offline window is short — proportional to the time to move the physical files — but must be coordinated with application owners.

Do not do this

Do not move physical files before updating metadata. If you move master.mdf before changing the startup parameters, or move a user database file before running MODIFY FILE, SQL Server cannot find the files at startup or when bringing the database online. Recovery requires editing startup parameters through SQL Server Configuration Manager with the service stopped, or booting from another instance.
Do not skip setting the default paths. The relocation of existing files does not change where SQL Server creates future databases. If the default paths are not updated, the next CREATE DATABASE, SSMS restore, or AlwaysOn join operation will write new files back to C:. The work needs to be done twice.
Do not treat tempdb the same as other system databases. Tempdb does not require physical file movement — SQL Server creates it fresh at every startup. Attempting to move tempdb files manually while SQL Server is running will fail because the files are held open. The correct procedure is ALTER DATABASE MODIFY FILE, then restart, then optionally clean up the orphaned files at the old location.
Do not place tempdb on the same volume as user data files. Tempdb experiences heavy concurrent write activity. Sharing a volume with user data files introduces I/O contention. Dedicate a volume to tempdb, or — on cloud VMs — use local ephemeral storage where available.
Do not perform this during business hours without a maintenance window. Each user database is offline while its files are being moved. If the files are large and the network path between source and destination is slow (for example, on a storage migration), the offline window can extend significantly. Estimate the move time against your RTO before scheduling.
Do not move the error log location unless intentional. The -e startup parameter controls where SQL Server writes its error log. It defaults to C: and is separate from database file paths. Leaving it on C: is acceptable if the error log volume is managed separately. Changing it requires the same startup parameter procedure as master.

Official references

Continue reading

Marios Pavlidis Principal Database Administrator