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


By:   |   Updated: 2015-01-08   |   Comments (5)   |   Related: More > Database Mirroring

Problem

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?

Solution

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 sd.name as DBName, saf.name 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
GO
ALTER DATABASE MODIFY FILE (NAME = LOGICAL_FILE_NAME, FILENAME = ‘the new location’)

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
GO

ALTER DATABASE MSSQLTipsDemo MODIFY FILE (NAME = MSSQLTipsDemo, 
FILENAME = 'G:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSSQLTipsDemo.mdf')

ALTER DATABASE MSSQLTipsDemo MODIFY FILE (NAME = MSSQLTipsDemo_log, 
FILENAME = 'G:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSSQLTipsDemo_log.ldf')

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


Last Updated: 2015-01-08


get scripts

next tip button



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.

View all my tips
Related Resources





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.

 

Regards,

Ahmad


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 08, 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 08, 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 08, 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"?



download


Recommended Reading

Configure SQL Server Database Mirroring Using SSMS

SQL Server Database Mirroring Status Check and Manual Failover T-SQL Scripts

Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

Fixing SQL Server Database Mirroring Expired Certificates

Change operating modes for SQL Server Database Mirroring





get free sql tips
agree to terms


Learn more about SQL Server tools