Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2015-01-08   |   Comments (5)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

 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

 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

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

 

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

 

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


Learn more about SQL Server tools