Problem
In the previous article, we moved SQL Server’s tempdb, model, and msdb system databases to a new file location. As the process is similar for all the above mentioned databases, we described it in one tutorial. Moving the master database files, however, is a bit different process and it needs to follow different steps. In this tip we will explain the steps to relocate an instance’s master database files and what else is needed to perform after moving all system databases.
Solution
It is assumed that we have already moved the tempdb, model, and msdb system databases files. The process of relocating these files is described in the first part of this article. In this, the second part, we will relocate the master database files and then will follow the steps required after moving all system databases.
Move SQL Server Master Database Files
In our example, we have the SQL Server master database files located in the default path. Our task is moving these files to a new location.
To get the current location of the master database files, we run the following query:
SELECT name AS FileLogicalName, physical_name AS FileLocation FROM sys.master_files WHERE database_id = 1
We use the WHERE clause in the statement to retrieve only the master database files information (database_id=1). Now, we can see the data and log file(s) location for the master database:
The same information can be found by right-clicking on the master database in SQL Server Management Studio (SSMS) and choosing “Properties” > “Files” and look at the “Path” field:
Suppose our new path needs to be “C:\MSSQL\SystemDatabases”, so we need to set this path. Unlike the case of moving other system database files, we cannot change this path with the ALTER DATABASE command. It can be changed in “SQL Server Configuration Manager” where we can right-click on the corresponding SQL Server instance name then choose “Properties” > “Startup Parameters”:
The startup parameter starting with -d is the data file path of the master database and the parameter starting with -l is its log file path. These paths can be changed by clicking on them in the “Existing Parameters” field and then in the “Specify a startup parameter” change the path to the new value and then click “Update”:
Having changed both the data and log paths, we click “Apply” then “OK”. The message will inform that all changes will take effect after restarting the service:
So, in Configuration Manager, we stop the corresponding SQL Server service:
While the service is stopped, we will physically move the master database log and data files to the new location:
After the files are copied to the new location, we can start the SQL Server instance:
If all the above steps are followed correctly, the instance will start successfully. We can check the new paths of the master database files by using the query above or via SSMS:
Updating Registry Settings
Although SQL Server starts successfully, it is recommended to perform one more step after migrating the master database. The SQLDataRoot value in the registry should be changed to the new path in order to avoid patching and updating failures. To do that, we enter the Windows registry just by typing “Regedit” in the Windows search and click on the “Registry Editor”:
Then, we locate SQLDataRoot under “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance ID>\Setup”(where the <instance ID> is the corresponding instance ID. In our case, it is MSSQL12.MSSQLSERVER). In the SQLDataRoot, we change the “Value data” from the old path to the new:
Follow-up steps after moving all system databases
After moving all system database files to a new location which is a new drive, volume, or a server with a different drive letter, we need to perform a couple of additional steps.
The SQL Server Agent log path should be changed. Otherwise, the Agent will fail to start.
Also, the database default location should be changed. If the old default path does not exist anymore, the creation of a new database will fail. In our example, we just moved the system database files within the same drive, just to another folder. Anyway, we will describe these steps to illustrate the complete process of moving system databases. First, let’s change the Agent log – SQLAGENT.OUT file’s path. It can be found in SSMS, under SQL Server Agent > Error Logs > Properties. Here, we can set the new path which in our case is “C:\MSSQL\LOG”:
Now, we will change the database default location. It can be changed by right-clicking on the instance name, choose “Properties” and then “Database Settings”:
So, here we can change a new database’s data and log files default locations as well as the databases’ default backup location. After changing these paths, an instance restart is required:
Conclusion
To conclude, in this article we have learned the steps of moving the master database files to a new location as well as the additional steps required after moving all system databases. As we can see, migrating the master database requires some extra steps compared to the other system databases.
Next Steps
For more information, please follow the links below:
