Question: What is the master database in SQL Server?
Answer: It stores instance-level metadata, including logins, configuration and records of other databases and their file locations. SQL Server depends on it to start.
A reader once emailed me saying that master had been “dropped.” That is an alarming description, but SQL Server does not allow an ordinary DROP DATABASE to remove master. Missing files, corruption and startup problems need to be identified precisely before deciding how to recover. My first reaction was to explain why this database matters.
Master records instance-wide information such as logins, endpoints, linked servers and configuration settings. It also records the other databases and their file paths. It does not contain every database’s user tables, and current SQL Server system-object definitions reside physically in the read-only Resource database.
Know the system databases
- master: instance-level organizing information.
- model: template for newly created databases.
- msdb: SQL Server Agent jobs and other service metadata.
- tempdb: temporary objects and workspaces.
- Resource: system-object definitions, normally not displayed as an ordinary user database.
A distribution database is created when replication distribution is configured. It is not a default database on every newly installed SQL Server instance.
Back it up before you need it
Keep a current full master backup, particularly after changing databases, instance configuration or logins. My master backup practices post explains the habit. Keep application objects in user databases, rather than filling master with them.
If master becomes unusable, recovery is not a casual repair command. Depending on whether the instance can start, the supported route involves restoring a suitable backup or rebuilding system databases before restoring the required metadata. A rebuild affects more than master, so prepare the version, patch and recovery requirements carefully. The older restore master walkthrough is useful context, and master log growth is a separate issue to investigate.
The word “master” still makes me think of Yoda. My favorite remains, “Do. Or do not. There is no try.” For databases, I would add: practice the recovery before it is an emergency. And keep passing on what you have learned, which was the spirit of the original post.
Reference: Master’s purpose, restrictions and backup guidance, System databases.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
5 Comments. Leave new
System databases can’t be dropped. How did he do it?
Good question. I will see if I can find answer of it.
These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.
Master files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.
Did you try deleting mdf and left files when the dub was online? Did it work?
And master database or for that matter system databases can’t be detached using sp_detach_db.