By: Eric Blinn
Overview
In this section we will cover common questions like: what is the purpose of the master database, does SQL Server need the master database, can you drop or rename and more.
What is the SQL Server master database and its purpose?
The master database is one of 4 visible system databases with model, msdb, and tempDB being the other 3. Its purpose is to store the list of server level settings, like those from sp_configure, and server level metadata, like server logins/passwords and linked servers. During startup it provides the service with critical configuration information. It is also the system of record for all of the other system databases files and all of the user database names and file locations.
This database is the most sensitive system database used by the SQL Server engine to operate the instance. Other than the natural interaction with master by logging in or reading settings, it should generally be left alone to avoid causing problems within the instance. This idea of leaving the master database alone will be a recurring theme of this tutorial.
Can I run SQL Server without a master database?
The master database is the first database considered by a SQL Server instance when it starts up. Its data and log files are even fed to the SQL Server binary as startup parameters. SQL Server cannot start or execute without a master database. If a service start command was issued while the master database files were not available for any reason, the service would fail to start.
This screenshot shows the error received after attempting to start SQL Server with a purposely misspelled master database file name parameter.
Is the version of the master database unique to the version, edition, and patch level of SQL Server?
The master database is slightly different between major editions of SQL Server, but like other user databases, it can be attached to newer major versions. It cannot be attached to lower major versions. Upon startup the SQL Server binaries will reconcile any differences and upgrade the database as necessary.
The master database does not differ between editions (enterprise, standard, express, etc.) of SQL Server.
While there might be slight differences in the master database between patch levels it can be moved between patch level of the same major release -- even if the target instance is an older patch level. The most common scenario for this would be a failover cluster instance where 2 or more nodes are not on the same patch level. The failover operations would succeed between the nodes in any direction, but could take an excess amount of time to failover while SQL Server reconciles these differences.
Most of the version specific code objects have been moved out of master and into mssqlsystemresource, an invisible and read-only system database. That process is designed to ease upgrades by allowing SQL Server to swap out an older mssqlsystemresource database for a newer one while leaving the master database mostly alone.
Can you drop the SQL Server master database and should you do this for any reason?
SQL Server cannot execute without a master database. As such, it cannot be dropped or taken offline. Furthermore, this author cannot think of any reason one would want to drop the master database.
Imagine trying to run an instance of SQL Server without values for default settings. Without any server logins. Without any databases. It’s simply not possible.
Can you rename the SQL Server master database and should you do this for any reason?
The master database cannot be renamed. SQL Server expects the database to be online and available with the name master. Again, this author cannot think of any reason to try to rename the master database.
If a user or developer wants to create their own "master" database and requests that the system database be renamed to support this request that request must be denied. They can give their database a different name. They should absolutely not be allowed to use the master database to store their objects.
Last Update: 10/8/2019
