Move Database Files for a SQL Server Mirrored Database Without Impacting Database Mirroring

By:   |   Comments (5)   |   Related: > Database Mirroring


One of our production SQL Server database disk drives was running out of space. We had other drives available with a lot more space, so we wanted to move all of the SQL Server database files to the other disk.  Unfortunately we are not able to detach the database in order to move the files, because the database is using database mirroring and stopping the mirroring and reconfiguring is not an option as it is big effort to recreate it again since the principal and mirror databases are hosted in two different data centers. So, how could we move the SQL Server database files without the need to stop and re-create database mirroring?


As you may know, SQL Server database metadata is stored in the master database, which is required during the SQL Server service startup and it includes the location of the physical files for each database.

The location of the database files (i.e. path) can be viewed from the Files tab in the database Properties window in SQL Server Management Studio, in addition to the Logical and Physical names of the database files as shown below:

Migrating Database Files of Principal Database

You can also get this information from querying these system tables:

-- shows info for all databases
SELECT as DBName, as LogicalName, saf.filename
FROM master.sys.sysaltfiles saf 
INNER JOIN master.sys.sysdatabases sd ON saf.dbid = sd.dbid

-- shows file info for the current database
SELECT * FROM sys.database_files

Changing the Database File Path for a SQL Server Database Mirroring

The file path can be changed by applying the ALTER DATABASE command in the master database. This change will take effect after restarting the SQL Server service.  The syntax for the command is as follows:

USE master

Example to Change SQL Server Database File Path for a Mirrored Database

Assume that we want to move the MSSQLTipsDemo database from the current location to the G: drive, without having ro remove database mirroring. 

Follow these steps:

Step 1 - On the principal server, use the ALTER DATABASE statement in the master database to move the database files to the G: drive.  So for our example it would be:

USE master



Step 2 - Stop the SQL Server service. It is best to do this using SQL Server Configuration Manager as shown below:

Stop the SQL server service.

Step 3 - Move the database files to the new location. It is best to copy and paste the files then after your database is up and running again you can delete the old files.

Step 4 - Start the SQL Server service and you can check that mirroring is synchronized and the database is working fine.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, June 11, 2016 - 3:21:34 AM - Ahmad Yaseen Back To Top (41663)


 Thank you Midhun for your comment here.

Please note that the change we applied here is on the server level, which is changing the location preoperties on the master database, and the mirroring is on the database level, so no need to apply this change on the mirrored database. and if we manage to move the mirrored database files we need to drop the mirroring and create it again in the new location, as the mirrored database in Restoring mode.




Friday, June 10, 2016 - 4:39:56 PM - Midhun C N Back To Top (41659)

 Wouldn't this mean the metadata of the mirrored DB has also changed and the files need to moved to the new location in the mirrored server also?

Thursday, January 8, 2015 - 1:19:55 PM - Ahmad Yaseen Back To Top (35874)

Thanks eric81 for your comment.

No need to take the database offline as you will stop the SQL service while you are copying the database files.

Thursday, January 8, 2015 - 12:40:03 PM - eric81 Back To Top (35873)


Disregard my original post I ran a quick test and confirmed the database didn't have to be put in an 'offline' status... sorry.

Thursday, January 8, 2015 - 12:32:46 PM - eric81 Back To Top (35872)


Good article.   Now prior to moving the data files to the new drive do the databases need to be taken "offline"?

get free sql tips
agree to terms