Detailed steps on how to move master database files (mdf and ldf) from one location to another. This change requires a restart of your SQL Server Instance, so make sure to do this during a maintenance window.
- Open your SQL Server Configuration Manager
2. Right click on the SQL Server Instance -> select Properties
3. On the Properties tab -> select Startup Parameters.
4. Now click on each of the parameters in Existing parameters. You have to click on -d and -l parameters and update them with your new locations.
Example, in my case for data file
From -dC:\Program Files\Microsoft SQL Server\MSSQL15.SQL2K19\MSSQL\DATA\master.mdf
to -dF:\MSSQL\DATA\master.mdf
That is., You have to update something like below for data & log files. Make sure to click on Update and Apply for each file.
-dF:\MSSQL\DATA\master.mdf & -lF:\MSSQL\DATA\mastlog.ldf
5. Now stop SQL Server instance from configuration manager.
6. Move the master databases files to the new location, folder. Right click on the destination folder and give full access to the SQL Server service account.
7. Now start your SQL Server instance.
8. That’s all, master database files are now moved. Run the below query to verify the new path of master database.
use master
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files
where database_id = db_id('master')
go9. One final step is to make sure to update the new master data location in registry. This is required to avoid patching, upgrade failures. Open the system’s Registry Editor (regedit.msc), and under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup
update the value for String SQLDataRoot , as shown below
Note that unlike other databases, we do not have to update the new location of master files in the master database.
Click on the below link to learn more on moving other system databases and user databases in SQL Server.
One thought on “How to move master database files in SQL Server”
Comments are closed.