SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing the Path for the Master Database

By Sadan Kumar Budde,

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.

 
Total article views: 647 | Views in the last 30 days: 4
 
Related Articles
BLOG

SQL Server – Changing Default Database Location for Server

When you create a new database in SQL Server without explicitly specifying database file locations, ...

BLOG

Change the name of master database files – SQL Server on Linux

In my recent blog, I discussed about how to move master database to another location. One of the rea...

BLOG

How to change TempDB system Database files location?

Steps to change TempDB system Database files location :- 1) Check current file location of TempDB ...

BLOG

How to Move Master Database to Another Location – SQL Server On Linux

With the release of SQL Server 2017 CU4, you can use the mssql-conf utility to move the master datab...

FORUM

How to change Default Database locations without a restart

How to change Default Database locations without a restart

Tags
 
Contribute