By: Ahmad Yaseen | Comments (5) | Related: > 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:
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 DATABASEMODIFY 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.
- To copy the database files to the new location, check out COPY, XCOPY, ROBOCOPY.
- Check out this related tip - How to move database files of a Mirrored SQL Server Database.
About the author
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
