SQL Server Master Database Facts

Problem

The master database, as its name suggests, is the most important system database in SQL Server. Having said that, it is important to mention that all other system databases are also essential. However, the master database contains all the information about the SQL Server system, including information about initialization, configuration settings, instance-related metadata, information about all other databases, such as the location of database files, and so on. This is why the existence and proper functionality of the master database is crucial for any SQL Server instance. In this article, we will illustrate five facts about this vital database.

Solution

Well, by the five facts below, we will shed light on some important features and specifications of the master database. Let’s start.

Fact 1 – Logon accounts and other instance-wide metadata are stored in the SQL Server master database

Have you ever thought about where logins are stored? For example, do you know where the "sa" login and its password are stored? Well, logins are stored in the master database. While in the case of SQL logins, their password hashes are also stored in the master database, for Windows logins, SQL Server relies on Windows for the authentication. Therefore, the "sa" account and its hashed password can be found in the master database. We can get this information by running the T-SQL code below:

SELECT name AS LoginName,  password_hash 
FROM sys.sql_logins 
WHERE name='sa'

We can see the login name and the password hash from the SQL query:

Not only logon accounts, but also all the system-level information is stored in the master database. Linked servers and endpoints are two other such examples. The question may arise: where can we see these tables where all this information is stored? When we expand the "Tables" of the master database in SQL Server Management Studio (SSMS), we cannot see the related tables. There are no tables under the main "Tables" (if we have not created any manually) and under "System Tables", there are only four tables as shown below:

This is because the tables containing the system-level information are hidden and are not visible from SSMS.

Fact 2 – System objects are not stored in the master database

There is a common misconception that system objects are stored in the master database. Well, while in older versions of SQL Server, such as SQL Server 2000, the master database contained the system objects, in newer versions this data is no longer stored in the master database. Starting with SQL Server 2005, these objects are stored in the resource database, which is a hidden, read-only system database. So, the master database does not contain system objects, such as the data in sys.objects, anymore.

Fact 3 – If the master database is not available, it is not possible to start SQL Server

As the initialization information, information about other databases in the instance and their files’ locations are stored in the master database, it is not possible to start the instance if the master database is not available. We can illustrate this behavior by making the master database unavailable. To do that, we can stop the instance (it is strongly recommended to perform all these tests only on test instances), move the master database files (data and log or only one of them) to another location and then try to start the instance.

To stop the instance, we open SQL Server Configuration Manager and choose the corresponding instance, right click on it and select Stop.

In the instance’s startup parameters using the Properties option shown above, we can find the location of the master database files.

So, let’s copy the data file of the master database to another location.

Then, let’s try to start the instance, by right clicking on it and selecting Start.

The instance will not start and we get the following error.

If we open the ERRORLOG file, we can find the description of the reason there:

If we move the master.mdf file back to its original location, the instance will start successfully.

Fact 4 – It is possible to create user objects in the master database, but it is not recommended

While we can create user objects such as tables, stored procedures, etc. in the master database, it is not recommended to do that. Having said that, in some cases, some people decide not to follow the best practices and create user objects in the master databases for some purposes. However, sometimes, user-defined objects are created in the master database by mistake. This usually happens because when we open a new query window in SSMS, the default database is the master database (unless it is changed). In this case, if there is not a "USE <database>" statement in the T-SQL code, the objects will be created in the master database.

Let’s open a new query window and without changing the database run the code below.

CREATE PROCEDURE uspTestProc @pID INT 
AS 
BEGIN 
 SELECT * 
 FROM TestTable 
 WHERE ID =@pID 
END 
GO 
  
CREATE TABLE TestTable (ID INT); 
  
INSERT INTO TestTable(ID) VALUES (1), (2), (3)

So, we can see that the code runs in the master database and we have a table and a stored procedure created in the SQL Server master database as shown below.

To find the user-created objects in the master database, we can run the following Transact-SQL code.

SELECT *FROM master.sys.objects
WHERE is_ms_shipped=0

Where is_ms_shipped is either 0 or 1 (bit data type field) and shows whether objects are created by an internal SQL component (1) or not (0). In our case, we can see that we have two user created objects.

Fact 5 – It is possible to create only full backups of the master database

It is recommended to have a fresh backup of the master database. Particularly, it is important to perform the master database’s backup when we create, drop, or modify new databases, logon accounts and change configuration values. Additionally, if you have created user objects in the master database (which is not recommended as mentioned above), it is reasonable to backup the master database according to the changes of these user-created objects. It is not possible to take a transaction log or differential backup of the master database.

If we try to perform a backup operation on the master database, we can see that the only available option is the full backup.

In this case, we can also see that the recovery model is "SIMPLE".

Let’s change it to "FULL" and see what will be changed in terms of backup options. In the "Database Properties" > "Options" of the master database we set it to "FULL" as the recovery model and click "OK".

Now, let’s try to perform a backup operation again. As we can see below, although the recovery model is "FULL", transaction log backup (differential as well) is not possible and the only option is the full backup.

Conclusion

To sum up, we have explored five interesting facts about the SQL Server master database. Specifically, we have learned that the master database contains the logon accounts and the password hashes of the users with SQL authentication. Also, we have learned that the master database does not contain system objects (since SQL Server 2005 they are stored in the resource database). We have seen by a simple example that it is not possible to start the SQL Server instance if the master database is not available. Additionally, by another example, we have checked that it is possible to create user objects in the master database. Finally, we have tested and made sure that the only possible backup of the master database is the Full backup.

Next Steps

For more information, please follow the links bellow:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-05-04

Five facts about the SQL Server Master Database