SQL Server Master Database Location


By:
Overview

In this section we will cover questions like: what files does the master database use, where are the files stored on disk, can the files be moved and more.

What physical files, names, locations, etc. support the SQL Server master database?

The master database is made up of 2 files -- one data file and one log file.  The data file has the logical name "master" and the default file name of "master.mdf".  The log file is "mastlog" and "mastlog.ldf" respectively. 

This is the minimum number of files for any database and master is no exception.  As such, neither of these files can be dropped.  Neither an additional data file nor log file can be added to the master database.

This screenshot shows that an attempt to add either a data file or log file to the master database will result in an error.

SQL Server will let you rename either the physical or logical names of these 2 files, but there is really no reason to complete an operation like this.  It serves no purpose and would add an unnecessary layer of complexity to the environment.

This screenshot shows that the MODIFY FILE command will allow the SQL Server master database logical file names to be changes, but makes it VERY clear that this is not an operation that should be executed.

Where do I find the SQL Server master database on disk and in SSMS?

There are a few ways to find the files in SSMS.  They can be read from the sysfiles DMO.

select name, filename from master.sys.sysfiles

The files are also listed in the database properties window. If you right click on the master database in the SSMS Object Explorer and select Properties the following window will open.

The "Files" pane of the master database properties window shows the 2 files that make up the master database.

The files can be found listed in the SQL Server configuration manager.  Under "Services" find "SQL Server (INSTANCENAME)" and open the properties window.  Browse to the "Startup Parameters" tab.  The -d parameter is the master database data file.  The -l parameter is the master database log file (FYI – that’s a lowercase L, not an I).  Also, the -e is the plain text error log file.

This screenshot shows the service properties window from within the SQL Server configuration manager.  It lists the start up parameters for SQL Server including the master database data and log files.

When browsing on disk, the files look like any other set of database files.

sql server master database data and log files

Does it make a difference where the SQL Server master database files reside (do they need to be on C:\, SAN, etc.)?

The master database files should be stored with the other database files of the same type.  This generally means NOT on the C: drive.  The screenshots in this post are showing the C: drive only because they are from a local instance on this author’s laptop that contains only a C: drive. 

Starting with SQL Server 2012, the installer offers a choice about where to store the system database files.  Prior to that they would be installed with the SQL Server binaries and have to be moved to the target location post installation.

Can the SQL Server master database be moved?

Yes!  The master database files can be moved.  The are 2 methods to move the master database files, but they both follow the same basic steps.  The process to move the master database is as follows.

Step 1.  Inform SQL Server that during the next startup of the service the master database data and/or log file will be somewhere else. 

During this step it will allow the file name to be changed, but it is not advised to change the physical file name or extension.  This is where there are 2 choices to complete the task. Neither option actually moves the files, they will need to be manually moved in a later step.

The following code will change the expected location of the master database data and log files respectively from wherever they currently are to the root C drive.  It assumes the default logical names.

ALTER DATABASE [master] MODIFY FILE ( NAME = master , FILENAME = 'C:\master.mdf' )

ALTER DATABASE [master] MODIFY FILE ( NAME = mastlog, FILENAME = 'C:\mastlog.mdf' )

Executing these commands will give a very clear output message.

The file "master" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "mastlog" has been modified in the system catalog. The new path will be used the next time the database is started.

The other method to change the expected location of the master database data and/or log files is to modify the startup parameters of the SQL Server service using the Configuration Manager utility.  Simply click on the file that needs to be moved, D or L, type or paste in the new path, then click update.

This screenshot shows the startup parameters tab of the SQL Server service with the "Update" button highlighted.

Applying a change to one of these parameters gives another very clear output message.

This screenshot shows the output message after changing a startup parameter of the SQL Server service.  It reads "Any changes made will be saved; however, they will not take effect until the service is stopped and restarted."

Step 2.  Now that SQL Server has been informed of the new location(s), stop the SQL Server service so that it will release its lock on those 2 master database files.

Step 3.  Move the file(s) to the intended location(s) that were given to SQL Server in step 1.

Step 4.  Start the SQL Server service.

SQL Server should start right up.  In the event it fails, the UI is unlikely to provide a useful message.  The Application Log in the Event Viewer application (if using Windows) usually provides much clearer guidance as to the problem.


Last Update: 10/8/2019




Comments For This Article




Thursday, December 19, 2019 - 4:58:17 AM - Rune C. Foyn Back To Top (83477)

In this article I think something should be written about what file permissions are required for the account running the SQL Server service.



download














get free sql tips
agree to terms