Five facts about the SQL Server Master Database


By:   |   Updated: 2021-05-04   |   Comments   |   Related: 1 | 2 | 3 | More > System Databases


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:

query output

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:

master database objects

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.

sql server configuration manager

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

sql server configuration manager

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

database files

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

sql server configuration manager

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

sql server configuration manager

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

sql server error log

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 [email protected] 
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.

create objects

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.

query output

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.

backup master database

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".

database properties

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.

backup master database

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:



Last Updated: 2021-05-04


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips



Comments For This Article





download





Recommended Reading

Five facts about the SQL Server Model Database

Five Facts about the SQL Server Resource Database

Restoring the SQL Server Master Database Even Without a Backup

Track SQL Server TempDB Space Usage

SQL Server Backup and Restore History Queries














get free sql tips
agree to terms