SQL SERVER – Importance of Master Database for SQL Server Startup

October 31, 2007 by pinaldave

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

What is the purpose of Master database?
Should we backup Master database?
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 first time?
What happens if Master database is corrupted?

Answers to all of the questions are very much related. 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. Only Master database is the one which is absolutely must have database. Without Master database SQL Server can not 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 damaged Master database. Sometimes Master database is corrupted so much it can not start SQL Server at all and it can not be restored. SQL Server should be rebuild using command prompt and restored from latest backup once again.

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 8 Comments

8 Responses

on December 22, 2007 at 7:49 am | Reply Amol Solunkhe

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
on January 21, 2008 at 1:41 am | Reply Wasim

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?
on October 19, 2008 at 12:40 pm | Reply joedotnot

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… ?
on February 4, 2009 at 12:09 pm | Reply Sarnam

This is extremely good information about master database in other words we can say short and sweet matters .
thanks
on February 4, 2009 at 12:10 pm | Reply Sarnam

This is extremely good information about master database in other words we can say short and sweet description.
on March 20, 2009 at 4:29 am | Reply kumar

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
on August 18, 2009 at 9:35 pm | Reply Tim

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.
on November 9, 2009 at 12:04 pm | Reply Divya

Hi Sir,
The information is extremely useful. Could you please provide some information about how to restore master database.