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
If any corruption in master database,is it needed to reinstall the sql server???
Hi
Information on site is very useful to me. I have read this article of system databases article but it will be more useful, if u give something more about How to start SQL Server when master db corrupted and How to restore it.
Thanks
Pavankumar
hi all,
I hav to copy my database from one system to other while i try to copy the data folder in sql server it shows me a error master db file is open i checked everything and all files are closed can any one help me on this…………???
thanks……….
kishore ch
thanks you sir ,it is very helpful in dba learners
Hi all,
I have two servers in SQL 2008 R2, one for production and another for replication, replication is going to be using SRDF, reading about SRDF is byte level but my question is, if device E is on SAN and this is te device that replication will use, do I need to move my master database to E?
Does SRDF works with master database?
Is it dangerous that my master database be located in a SAN device?
Thanks.
Hi ,
I am clear what you discuss so far.Explain same for remaining system databases.If I delete the model ,temp and msdb.
bt u hav not told role of master databse it was vry commanly given answers which a novice can give….pls specify the exact and detailed ans.
Is it possible to take user database backups when master database corrupted ?
What happened when you tried to take a backup?
its very nice.i want to know about remaining system databases temp,msdb,model also.
Really great comments by all
Hi Pinal,
This is realy good information about Master DB,it’s very helpful to me.could you explain little more about MSDB,TEMPDB and MODELDB plase.
Thanks,
Sunanda.
Hello Pinal,
Thank you for your information. Its really helpful. Some days back i forgot the password of “sa” login of sql. Since then i was thinking of writing a tool that can
help me getting the password of “sa” and all the sql logins from MasterDB.
But i am not sure about where to start. I have got some software that do the same but all of them are “Paid”. So i thought of writing my own tool.
Can you give me some directions for that ??
Thanks & Regards,
Siddharth
The information is veryfull to me.can u explain how to rebuild sql server from command prompt to restore latest backups.
hello sir just want to knw can we work wid sql server without master databse …..
Hi dev,
Can i corrupt only page in master database.?
Just gave a good lecture on the same!Thanx.
hellow sir,
I am very happy by observing your valuable suggestions
dear sir, thanks a lot for your service, I read many articles of yours, but I have one doubt.
can we identify corrupted master database using smo?
it is my privilege ,if I got the reply.
thanks a lot sir
your’s
Ravishankar
It is very helpful….