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:
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:
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
- Try this in a test environment to see how this works and then failover the SQL Server database to see that these changes had no impact on the metadata for the other server and everything still works fine on both servers.
- To learn more about how to configure the SQL server mirroring, check out these SQL Server Database Mirroring Tips.
- Check out this related tip – How to move database files of a Mirrored SQL Server Database.
Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.
- MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015