How to Safely Migrate SQL Server Database Files

How to Safely Migrate SQL Server Database Files and Decommission a Storage Drive

Introduction

Moving SQL Server database files from one storage drive to another may appear to be a straightforward administrative task. However, relocating an entire SQL Server instance's storage dependencies involves significantly more than moving MDF and LDF files.

SQL Server maintains multiple types of files across its storage infrastructure, including system databases, user databases, temporary database files, transaction logs, Extended Events logs, SQL Server Agent logs, Full-Text Search resources and internal system database templates.

Failing to identify and relocate these dependencies can result in SQL Server startup failures, inaccessible databases, broken monitoring, failed SQL Server Agent jobs or problems during future SQL Server patching.

This article provides a practical, step-by-step guide for database administrators planning a SQL Server storage migration or disk decommissioning.

The examples focus on SQL Server 2022 running on Windows, although many of the techniques apply to other supported SQL Server versions.

1. Understanding SQL Server Storage Dependencies

Before migrating database files, DBAs must understand the different storage components used by a SQL Server instance.

Critical distinction: SQL Server database files and SQL Server installation binaries are not the same thing.

If SQL Server executable files or feature components are installed on the storage drive being removed, relocating MDF/LDF files alone is insufficient. The installation itself may need to be migrated to a new instance using a supported method.

2. Perform a Complete SQL Server File Inventory

The first step is identifying database files and their current physical locations.

Execute:

USE master;
GO

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalFileName,
    type_desc AS FileType,
    physical_name AS FileLocation,
    CAST(size * 8.0 / 1024 AS DECIMAL(18,2)) AS SizeMB
FROM sys.master_files
ORDER BY database_id, file_id;

To identify files on a particular drive:

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalFileName,
    type_desc,
    physical_name
FROM sys.master_files
WHERE physical_name LIKE 'X:\%'
ORDER BY database_id, file_id;

In this example, X: represents the storage drive scheduled for removal.

This inventory helps identify which data and log files need to move.

However, sys.master_files does not identify every SQL Server dependency. Extended Events, SQL Server Agent logs, Full-Text resources, registry settings and installation files must be examined separately.

3. Prepare the Destination Storage

A well-designed storage layout simplifies administration, monitoring and future maintenance.

For example:

Y:\SQLServer\
    SystemData\
    SystemLog\
    UserData\
    UserLog\
    TempDB\
    ExtendedEvents\
    ErrorLogs\
    Backups\

The directory structure should reflect the organisation's storage performance, capacity and recovery requirements.

For production systems, consider separate physical or virtual storage volumes for heavily used transaction logs and tempdb workloads.

Create the destination folders using PowerShell:

$Folders = @(
    'Y:\SQLServer\SystemData',
    'Y:\SQLServer\SystemLog',
    'Y:\SQLServer\UserData',
    'Y:\SQLServer\UserLog',
    'Y:\SQLServer\TempDB',
    'Y:\SQLServer\ExtendedEvents',
    'Y:\SQLServer\ErrorLogs',
    'Y:\SQLServer\Backups'
)

foreach ($Folder in $Folders) {
    New-Item -ItemType Directory `
        -Path $Folder -Force | Out-Null
}

Ensure the SQL Server service account has appropriate access to each destination.

For a default Windows instance using the standard service SID:

icacls 'Y:\SQLServer\SystemData' `
    /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'

Apply appropriate permissions to the other folders and validate the actual service identity before migration.

DBA best practice: Complete full database backups, confirm recovery procedures, record original file paths and plan a maintenance window before changing any database file locations.

4. Relocating User Database Data and Log Files

User databases are generally relocated using ALTER DATABASE MODIFY FILE.

First, identify their logical file names:

SELECT
    name,
    type_desc,
    physical_name
FROM sys.master_files
WHERE database_id = DB_ID('SampleDB');

Update the paths:

ALTER DATABASE SampleDB
MODIFY FILE
(
    NAME = SampleDB,
    FILENAME = 'Y:\SQLServer\UserData\SampleDB.mdf'
);
GO

ALTER DATABASE SampleDB
MODIFY FILE
(
    NAME = SampleDB_log,
    FILENAME = 'Y:\SQLServer\UserLog\SampleDB_log.ldf'
);
GO

The logical names shown are examples and must match the actual database metadata.

These commands only modify SQL Server's file location metadata; they do not physically relocate the files.

For a planned whole-instance storage migration:

1. Execute the file modification commands for every affected user database.

2. Stop SQL Server during the approved maintenance window.

3. Copy the data and log files to their new locations.

4. Verify file integrity and permissions.

5. Restart SQL Server.

6. Confirm the databases are online and using the new locations.

For an individual user database, an alternative supported approach is to take the database offline, relocate its files and bring it online again.

Additional planning is required for Availability Groups, log shipping, replication, FILESTREAM and memory-optimised data containers.

5. Moving the master, model and msdb Databases

The three persistent system databases require special attention.

Moving model and msdb

The normal file modification procedure can be used.

Example:

ALTER DATABASE model
MODIFY FILE
(
    NAME = modeldev,
    FILENAME = 'Y:\SQLServer\SystemData\model.mdf'
);
GO

ALTER DATABASE model
MODIFY FILE
(
    NAME = modellog,
    FILENAME = 'Y:\SQLServer\SystemLog\modellog.ldf'
);
GO

Similarly, update the msdb database file locations using its actual logical file names.

After stopping SQL Server, copy the physical files and restart the instance.

Following the migration of msdb, verify SQL Server Agent functionality, Database Mail, backup history and Service Broker configuration.

Moving master

Unlike other system databases, master uses SQL Server startup parameters to identify its files.

Open:

SQL Server Configuration Manager → SQL Server Services → SQL Server Instance Properties → Startup Parameters

The relevant parameters are:

Example:

-dY:\SQLServer\SystemData\master.mdf
-lY:\SQLServer\SystemLog\mastlog.ldf
-eY:\SQLServer\ErrorLogs\ERRORLOG

Update the existing parameters, stop SQL Server, copy the master files and restart the instance.

Always preserve the original startup parameters for rollback.

An incorrect master database path or inadequate file permissions can prevent SQL Server from starting.

6. Relocating tempdb Data and Log Files

SQL Server recreates tempdb whenever the Database Engine starts.

Therefore, unlike permanent databases, tempdb does not require physical file copying.

Identify all existing files:

SELECT
    name,
    type_desc,
    physical_name,
    size * 8.0 / 1024 AS SizeMB
FROM sys.master_files
WHERE database_id = 2;

Example relocation:

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = tempdev,
    FILENAME = 'Y:\SQLServer\TempDB\tempdb.mdf'
);
GO

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = templog,
    FILENAME = 'Y:\SQLServer\TempDB\templog.ldf'
);
GO

If tempdb contains additional data files, execute a corresponding statement for every file.

Restart SQL Server to recreate tempdb at the new location.

Validate:

USE tempdb;
GO

SELECT
    name,
    physical_name,
    size * 8.0 / 1024 AS SizeMB
FROM sys.database_files;

Performance consideration: A storage migration is an opportunity to review tempdb file count, file sizes, autogrowth settings and storage latency. However, file sizing should be based on observed workload rather than arbitrary defaults.

7. Moving Extended Events Diagnostic Files

Extended Events is widely used for SQL Server performance monitoring, query analysis, deadlock investigation and availability diagnostics.

Extended Events sessions configured with the event_file target write .xel files.

Identify the currently running sessions and their file locations:

SELECT
    s.name AS SessionName,
    CAST(t.target_data AS XML).value(
      '(EventFileTarget/File/@name)[1]',
      'nvarchar(4000)'
    ) AS FileLocation
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
    ON s.address = t.event_session_address
WHERE t.target_name = 'event_file';

For a custom Extended Events session, the destination can be changed by stopping the session, replacing its event-file target and restarting it.

Example:

ALTER EVENT SESSION [PerformanceMonitor]
ON SERVER STATE = STOP;
GO

ALTER EVENT SESSION [PerformanceMonitor]
ON SERVER
DROP TARGET package0.event_file;
GO

ALTER EVENT SESSION [PerformanceMonitor]
ON SERVER
ADD TARGET package0.event_file
(
    SET filename =
      N'Y:\SQLServer\ExtendedEvents\PerformanceMonitor.xel',
        max_file_size = 100,
        max_rollover_files = 10
);
GO

ALTER EVENT SESSION [PerformanceMonitor]
ON SERVER STATE = START;
GO

The session name and configuration are illustrative. Preserve the original event target settings when changing a production session.

Built-in sessions such as system_health and AlwaysOn_health may use the SQL Server default log directory.

When relocating these sessions, review the effective target paths, startup configuration and SQL Server error log directory rather than assuming every session has a separately configured location.

Historical Extended Events files may also need to be archived before the source drive is decommissioned.

8. Understanding SQL Server 2022 Internal Template Files

SQL Server 2022 introduces additional internal files associated with contained Availability Group functionality.

These may include:

model_msdbdata.mdf
model_msdblog.ldf
model_replicatedmaster.mdf
model_replicatedmaster.ldf

These files are different from the normal model.mdf, modellog.ldf, MSDBData.mdf and MSDBLog.ldf files.

They support internal templates associated with contained Availability Groups.

One challenge for DBAs is that these files may not appear through the standard database inventory queries used for normal system and user databases.

Consequently, a database file inventory based solely on sys.master_files may not capture every internal file in the SQL Server data directory.

Important: Do not delete these files or assume conventional ALTER DATABASE MODIFY FILE statements will work. Microsoft’s standard system-database relocation documentation does not provide a general supported relocation procedure for these internal template files.

Before removing the storage containing them, determine whether the instance installation and internal template paths still depend on that drive. Use a tested, vendor-supported migration approach where necessary.

This is particularly important during complete storage-drive decommissioning, where SQL Server installation components must also be accounted for.

9. Full-Text Search Files and Dependencies

SQL Server Full-Text Search supports advanced text-based queries and indexing.

Modern Full-Text catalogs are integrated with SQL Server databases and move with the corresponding database files.

However, some Full-Text Search language resources, including thesaurus XML files, can reside in the SQL Server installation directory.

Example:

...\MSSQL\FTData\

These files may include:

tseng.xml
tsfra.xml
tsglobal.xml

Check whether Full-Text Search is installed:

SELECT
    SERVERPROPERTY('IsFullTextInstalled')
        AS IsFullTextInstalled;

Then identify databases with Full-Text Search enabled:

SELECT
    name,
    is_fulltext_enabled
FROM sys.databases;

DBAs must distinguish between Full-Text indexes stored in databases and Full-Text resources belonging to the SQL Server installation.

Moving database files does not automatically relocate every Full-Text installation component.

Avoid manually changing or deleting installation files unless the migration procedure is supported and the resulting configuration has been validated.

10. Other SQL Server Dependencies Often Overlooked

Removing a storage drive requires examining more than database metadata.

DBAs should review:

• SQL Server Agent error logs and job output files.

• SQL Server backup locations and maintenance plans.

• Extended Events and SQL Server Audit destinations.

• SQL Server trace and diagnostic files.

• Full-Text Search components.

• Database Mail and SQL Server Agent functionality.

• SQL Server default data, log and backup directories.

• Database snapshots and specialised storage containers.

• Registry settings and SQL Server installation dependencies.

• Windows services, scheduled tasks and third-party monitoring applications.

For SQL Server Agent jobs, search for old drive references:

USE msdb;
GO

SELECT
    j.name AS JobName,
    s.step_name,
    s.command
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps s
    ON j.job_id = s.job_id
WHERE s.command LIKE '%X:\%';

This helps identify scripts or job steps that may fail after the original drive is removed.

It is also important to check the SQL Server service executable paths. If installation binaries remain on the old drive, database relocation alone does not complete the migration.

11. Post-Migration Validation

A successful SQL Server service startup is not enough to declare a storage migration complete.

DBAs should validate database availability, actual file paths, SQL Server Agent functionality, diagnostic logging and application connectivity.

Check database status

SELECT
    name,
    state_desc
FROM sys.databases
ORDER BY name;

Check database file paths

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalFileName,
    physical_name
FROM sys.master_files
WHERE physical_name LIKE 'X:\%';

The expected result is zero rows for the retired drive.

However, this query does not validate Extended Events, installation resources, registry dependencies or application-level references.

Check SQL Server ERRORLOG

SELECT
    SERVERPROPERTY('ErrorLogFileName')
        AS ErrorLogLocation;

Check SQL Server Agent

Confirm that:

• SQL Server Agent starts successfully.

• Scheduled jobs are visible and functioning.

• Database Mail works, where configured.

• Backup jobs complete successfully.

• Maintenance plans and monitoring jobs do not reference old paths.

Validate application workloads

Perform application connectivity tests, execute representative database queries and confirm that transaction processing operates normally.

For databases with Full-Text Search functionality, test Full-Text queries as well.

Fresh backups should be completed and validated after migration.

12. DBA Best Practices for Storage Migration

A reliable migration should follow these principles:

1. Inventory before modifying. Identify databases, SQL Server services, log directories and storage dependencies.

2. Back up before migration. Verify that recovery procedures and backups are available.

3. Use a controlled maintenance window. Coordinate with application owners and infrastructure administrators.

4. Preserve original files. Copy files during the initial migration where practical and retain them until validation is complete.

5. Validate permissions. Confirm service accounts can access the new directories.

6. Treat system databases differently. Understand the startup requirements of master and tempdb.

7. Preserve diagnostic monitoring. Reconfigure Extended Events, Agent logs and audit targets.

8. Plan for rollback. Record original paths and startup parameters, and recognise that old file copies become stale once SQL Server resumes writing.

9. Verify installation dependencies. Never assume all components can be relocated through SQL commands.

10. Remove old storage only after sign-off. Validate applications, SQL Agent, backups, monitoring and recovery procedures before decommissioning the drive.

Conclusion

SQL Server storage migration is more than a simple file-copy operation.

Although the standard user and system database files can generally be relocated through documented SQL Server procedures, a complete disk decommissioning also requires reviewing Extended Events, SQL Server Agent logs, Full-Text resources, internal SQL Server 2022 template files and installation dependencies.

The greatest risks often come from overlooked configuration settings rather than the physical movement of database files.

By following a structured approach covering discovery, backups, configuration changes, physical file relocation, validation and rollback planning, DBAs can significantly reduce the risk of service disruption and database unavailability.

The key lesson for database administrators is simple: before retiring a SQL Server storage drive, verify that every database, service and supporting component is independent of that drive.

How Onsys Can Help

At Onsys Technologies, we support organisations with SQL Server administration, database migration, performance optimisation, high availability and managed database services.

Our SQL Server consulting and Remote DBA services help businesses plan and execute database infrastructure changes while reducing operational risk.

Whether you're relocating database storage, upgrading SQL Server, migrating databases to cloud infrastructure or addressing database performance issues, our experienced database specialists can assist with planning, implementation and post-migration validation.

Planning a SQL Server migration or storage infrastructure change?

Visit www.onsys.com.au to learn more about our SQL Server consulting and database administration services.