Mirrored Database: Move Files Without Breaking Mirroring

A mirrored database can move to a new drive without breaking mirroring. You change the file paths on the principal and stop both instances in the right order. Then you move the files and start the instances again. The T-SQL part is short. The order of the service restarts decides whether it works.

Before You Start

Plan for a short outage. The principal stops while its files move, so schedule the work in a maintenance window. Practice on a development pair first. Take a current full backup before you start, and check that the new drive has room for every file.

Database mirroring is a deprecated feature, and availability groups replace it. If your databases are in an availability group, this procedure doesn’t apply. It covers mirrored databases that are still in use.

Every statement below runs on the principal server. The mirror server only gets a service stop and a start.

Step One: List the Files

You need the logical and the physical name of every file. The demo uses a database named MirrorMoveDemo, which isn’t mirrored. The mirroring query returns NULL for it, and returns a state such as SYNCHRONIZED on a real mirrored database.

IF DB_ID(N'MirrorMoveDemo') IS NULL
CREATE DATABASE MirrorMoveDemo
ON PRIMARY (NAME = N'MirrorMoveDemo_data', FILENAME = N'D:\data\MirrorMoveDemo.mdf')
LOG ON (NAME = N'MirrorMoveDemo_log', FILENAME = N'D:\data\MirrorMoveDemo_log.ldf');
GO
USE MirrorMoveDemo;
GO
SELECT name AS LogicalName, type_desc AS FileType, physical_name AS PhysicalName FROM sys.database_files ORDER BY file_id;
SELECT DB_NAME(database_id) AS DatabaseName, mirroring_state_desc, mirroring_role_desc FROM sys.database_mirroring WHERE database_id = DB_ID();

Save this result. After the move, you compare against it.

Step Two: Point the Files at the New Folder

MODIFY FILE changes the path in the catalog. It doesn’t move anything. The new folder must exist before you run it. If the demo folder D:\data\Moved is missing, each statement below fails with Msg 5121.

Msg 5121, Level 16, State 1, Line 1
The path specified by "D:\data\Moved\MirrorMoveDemo.mdf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 2
The path specified by "D:\data\Moved\MirrorMoveDemo_log.ldf" is not in a valid directory.

Create the folder, then run one statement per file. In the demo, the folder is D:\data\Moved, and the SQL Server service account needs rights on it.

ALTER DATABASE MirrorMoveDemo MODIFY FILE (NAME = N'MirrorMoveDemo_data', FILENAME = N'D:\data\Moved\MirrorMoveDemo.mdf');
ALTER DATABASE MirrorMoveDemo MODIFY FILE (NAME = N'MirrorMoveDemo_log', FILENAME = N'D:\data\Moved\MirrorMoveDemo_log.ldf');

SQL Server prints one message for each file. It says the new path will be used the next time the database starts. Nothing has moved yet. Query both catalog views to confirm the change before any service stops.

SELECT name AS LogicalName, physical_name AS CatalogPath FROM sys.master_files WHERE database_id = DB_ID() ORDER BY file_id;
SELECT name AS LogicalName, physical_name AS DatabasePath FROM sys.database_files ORDER BY file_id;

Both views show the new path, although the files still sit in the old folder. The database stays online and keeps using the old files until its next start. If any file shows the wrong path, fix it now, before you stop a service.

Steps Three to Seven: Stop, Move, Start

The mirroring part uses services, not T-SQL. The sequence is the documented one, and the demo server has no mirrored pair, so these steps weren’t run here.

First, stop the SQL Server service on the mirror instance. Then stop the service on the principal. Use SQL Server Configuration Manager, a command prompt, or SSMS. Next, move the data and log files on the principal into the new folder. Use File Explorer or a copy command that keeps the permissions. The service account needs access to the new folder.

Start the principal first. For a short time, the database shows In Recovery, and then it comes online. Start the mirror instance last. After a few moments, the mirror catches up. With a witness, stopping the mirror first keeps an automatic failover from starting. The pair then shows as SYNCHRONIZED in high safety mode, or SYNCHRONIZING in high performance mode.

If the database stays in Pending Recovery, don’t improvise. A missing file, a wrong path or a permission problem causes it. Read the error log, and bring in an experienced DBA.

Step Eight: Check the Result

Run the step one query again. Every physical name must show the new folder. Then check the mirroring state on the principal and on the mirror. Both servers must show the state from the step above. Record the old and new paths in your change log, so that a later restore knows where the files live.

Is There a Way Without Downtime?

You could argue that a restart is too much for a database you mirrored to avoid downtime. That is a fair point. The procedure trades a short planned outage for a controlled move. Failing over to the mirror first is another route. It adds two failovers, and each needs its own rehearsal.

This post doesn’t cover moving the mirror’s own files. That is a different procedure, so rehearse it on a test pair first. Don’t copy the steps above onto the mirror server.

What to Remember

Run every ALTER DATABASE on the principal. Create the target folder first. Stop the mirror, then the principal, and start them in the reverse order. Check the file paths and the mirroring state afterward, and keep the step one result as proof.

Remove the demo database when you finish. The demo files never moved, so first point the catalog back at them. Then the drop deletes the files that exist.

ALTER DATABASE MirrorMoveDemo MODIFY FILE (NAME = N'MirrorMoveDemo_data', FILENAME = N'D:\data\MirrorMoveDemo.mdf');
ALTER DATABASE MirrorMoveDemo MODIFY FILE (NAME = N'MirrorMoveDemo_log', FILENAME = N'D:\data\MirrorMoveDemo_log.ldf');
GO
USE master;
GO
ALTER DATABASE MirrorMoveDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MirrorMoveDemo;

A mirrored database is not a pair of copies, it is a conversation a file move must not break.

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