The master database in SQL Server plays a critical role, and I often receive questions about it. Below, I’ve addressed some of the most common questions:
- What is the purpose of the master database? Should we back it up?
- Which database is essential for SQL Server to start up?
- Which default system databases are created when SQL Server is installed?
- What happens if the database is corrupted?
Answers to Common Questions – Master Database
The master is a system database that stores server-level metadata, such as information about logins, linked servers, configuration settings, and the location of other database files. It is fundamental to the operation of SQL Server.
When SQL Server is installed, the following system databases are created by default:
- master: Stores metadata and configuration information.
- model: Serves as a template for new databases.
- msdb: Used by SQL Server Agent for scheduling jobs, alerts, and backups.
- tempdb: A temporary workspace for storing intermediate results, tables, and indexes.
- resource: A hidden database that contains system objects used by SQL Server.
The distribution database is created only if SQL Server replication is configured.
Without the master database, SQL Server cannot start. Therefore, it is essential to back up the master database regularly to prevent catastrophic failure.
What Happens if it is Corrupted?
If the master database is corrupted, you can attempt to restore it from the most recent backup, provided SQL Server can still start in single-user mode. However, if the corruption is severe and prevents SQL Server from starting, you must rebuild the system databases using the setup command-line utility or the SQL Server installation media.
Once the system is rebuilt, you can restore the master database from a backup. This process ensures the server is restored to its previous configuration.
Regular backups of the master database are critical, especially when:
- Modifying server-level configuration settings.
- Changing database-level configuration settings.
- Adding or modifying login account details.
Master Data Management in Data Governance
Master data management (MDM) is a key component of data governance. It ensures consistency, accuracy, and control over reference and master data across the organization. MDM integrates with business processes, such as resolving discrepancies in data from multiple source systems. For example, when conflicting master data exists across systems, MDM processes help determine the “source of truth.”
While this blog focuses on technical aspects, exploring the process-driven side of MDM can offer additional value.
Final Thoughts – Master Database
Backing up all the databases should always be a priority. If you’ve encountered issues related to this or have questions, I’d love to hear your thoughts. Your feedback helps refine best practices and fosters knowledge sharing within the SQL Server community.
You can connect with me on LinkedIn.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
45 Comments. Leave new
Hi Pinal,
Ur information on site is very helpful to me. I have read about system databases article but It will be more helpful, if u give something more about How to start SQL Server when master db corrupted and How to restore it.
Also give some more detail idea about other system databases.
Thanks & Regards
Amol
Hi,
If i got master database corrupted & i want to rebuild that server,should i need to reinstall SQL Server then i need to go common prompt utilility?Which utility is that?
Why should i care if master database is backed up? Let’s say i backup all my user databases, and take it to another server, and restore them there… Do i have to take my master database with me ? NO.
So i can just re-install the whole SqlServer and a new master database will be recreated again.
Or did i miss something… ?
yes.Your way correct but how can you create users which contain earlier like System?and this need to ask them for passwords this is not good.
This is extremely good information about master database in other words we can say short and sweet matters .
thanks
This is extremely good information about master database in other words we can say short and sweet description.
Hi,
the given info is very helpful and can you please give more details about how to start the database from command prompt when master DB is corrupted.
kumar
I am doing an hourly differential backup on various databases on my server, including the MASTER database. MASTER changes during the day, even when no new database objects are being created; so MASTER must contain some dynamic metadata about the database objects, not only their configuration/structure.
Yes — the MASTER database is dynamic by the nature of its purpose. Whether new database objects are being created (or not), it DOES contain dynamic information. Because SQL server will not start if MASTER is sufficiently corrupted, it amazes me how many places on the Internet tell novices to create sample databases in MASTER so they can train with T-SQL.
This is dangerous as a novice may inadvertently corrupt MASTER.
Hi Sir,
The information is extremely useful. Could you please provide some information about how to restore master database.
It’s really helpfull to me.
Thanqu very much
can you give me detailed information about
what to do when master database is correpted?
waiting for ur replay…..
Thanqu
Hello Satish,
Do you have usable backup of master database to restore? If you have then you can restore the master database. For that you will have to start the SQL Server in single user mode using command:
NET START “MSSQLSERVER” /m
If you not have backup then use the rebuildm utility to create a new master database then attach all other database.
Regards,
Pinal Dave
after restoreing master how userdatabases will back in sql server..we have restore or automatically will come up please share ur reply
this information you provide is good but not best………..
u did not provide what type of information master database stored .. & why master database is used…….
this is valuable information
hai,
i have confusion about what is the difference between master database and resource database?…
how to see the resource database..
thanks and regrads
as reddy
I understand the safe game is to restore the database backups to a diff server and then bring it back to the existing one.
I want to know more details about system databases like tempdb,model,msdb. And let me know backup the master database is must ? If we fail to do so,cant we startup the instance?.Its enough only backup the master database or any other system database?
Dear Pinal,
I have some questions regarding SQL Server.
1.What is the output of the following statement:
select * from aer1,aer2 (if aer1 has 10 records & aer2 has 6 records)
without any condition but joining the tables.
2.delete from table1,table2
What happended when you tried to run the queries?
First query results to a cross join and second query will delete all rows from two tables specified
Hi Pinal,
This article is very helpful for me.
Thanks for such a great article.
Please provide in depth detail about how to restore the master database fro cmd utility.
hi pinal
thanx for providing this type of inormation
i am fresher,it will b very helpfull for me.
Hi Pinal,
First of all, I would like to thank you for all your efforts towards helping SQL server professionsals.
I have a question:
1. System stored procedures are stored in Master or MSDB database.
I can find the system stored procs in both master and msdb db’s. So i am very much confused about the answer.
Hi Pinal,
please tell me what is diffrence between Master and resource databases