By: Eric Blinn
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.
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.
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 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.
When browsing on disk, the files look like any other set of database 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 "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.
Applying a change to one of these parameters gives another very clear output message.
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.
