SQL SERVER – Importance of Master Database for SQL Server Startup

I have received following questions many times about master database. I will list all the questions here and answer them together.

What is the purpose of Master database? Should we backup it? Which database is must have database for SQL Server for startup? Which are the default system database created when SQL Server 2005 is installed for the first time? What happens if Master database is corrupted?

Answers to all of the questions are very much related. The master database is system database and it contains information about running server’s configuration. When SQL Server 2005 is installed, it usually creates master, model, msdb, tempdb resource and distribution (last two depends on version of SQL Server) system database by default. The only Master database is the one which is absolutely must have a database. Without this database SQL Server cannot be started. This is the reason it is extremely important to backup Master database.

If Master database is corrupted it should be restored from the latest backup, considering SQL Server was able to start with a damaged Master database. Sometimes it is corrupted so much it cannot start SQL Server at all and it cannot be restored. SQL Server should rebuild using command prompt and restored from the latest backup once again.

I am very much interested to know the feedback of readers who have asked questions regarding this issue.

If any database is corrupted it should be restored from the latest backup, considering SQL Server was able to start with a damaged database. Sometimes when this database is corrupted so much it cannot start SQL Server at all and it cannot be restored. SQL Server should rebuild using command prompt and restored from the latest backup once again.

Backup of the this database should be made for:

Changing server-level configuration settings;

Changing database-level configuration settings; and

Changing any logon accounts details.

Where does master data management fall within a data governance approach? Master data management is one area within the data governance process that focuses on the management of reference and master data. Master data management is combined with a fair amount of business process and I will leave the process aspect for another author. An example of a business process within master data management would be the decision on which data source is considered the truth when a discrepancy in master data arises between two source systems.

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.

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.

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.

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?

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.

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…………???

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.

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 ??

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.