Recently we came across a situation where we had to change the path of the system databases of a SQL Server. This was installed awhile ago on the C: Drive. We needed to move the system databases to the E: drive to avoid space issues as the databases were growing every day, and we did not have enough space to grow the files on the existing drive.

The ALTER DATABASE command would work for moving all the user databases and some system databases, including model, msdb and tempdb, but changing the path of the master database required a different strategy. This article will show how we can move the master database to a new location.

Moving the Master Database

Let us first get the current location of master database using the below query.

SELECT name, physical_name AS CurrentPath 
FROM sys.master_files  
WHERE database_id = DB_ID('master');  
go

This gives us results similar to this:

Next we can open SQL Server Configuration Manager. Select SQL Server Services and right click for the Properties of the respective SQL Server for which we are trying to make changes.

Under the Advanced tab, look for Startup Paramerters. This has the path of the master database mdf and ldf and the ERRORLOG files. In the latest versions of SQL Server, Startup Parameter is a new tab when we right click on Properties of SQL Server. In older versions, this is on the Advanced tab.

The parameter values for the master data files has to be updated to the new location. Be sure that the parameter names are included (-d, -l). In this case, the old location was:

-dC:\SqlData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

-lC:\SqlData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

The new location is:

-dE:\DATA\master.mdf

-lE:\DATA\mastlog.ldf

Now we must stop the SQL Services. Once that is complete, move the physical data files of the master database to the new location specificed in the startup parameters, E:\DATA\ in my case.

We can now start the SQL Services. SQL Server should running normally as it was earlier without any issues as location of the master databases is picked up from registry atomatically after we made changes in the startup paramerters. Check for the latest path of the masater data files by running the same query from Step 1.