SQL SERVER – Importance of Master Database for SQL Server Startup

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)

45 thoughts on “SQL SERVER – Importance of Master Database for SQL Server Startup

  1. 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

    Like

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

    Like

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

    Like

    • 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.

      Like

  4. 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

    Like

  5. 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.

    Like

    • 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.

      Like

  6. 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

    Like

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

    Like

    • after restoreing master how userdatabases will back in sql server..we have restore or automatically will come up please share ur reply

      Like

  8. 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…….

    Like

  9. 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

    Like

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

    Like

  11. 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

    Like

  12. 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.

    Like

  13. 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

    Like

  14. 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……….

    Like

  15. 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.

    Like

  16. Pingback: SQL SERVER – Importance of Resource Database – Quiz – Puzzle – 18 of 31 « SQL Server Journey with SQL Authority

  17. 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.

    Like

  18. 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

    Like

  19. The information is veryfull to me.can u explain how to rebuild sql server from command prompt to restore latest backups.

    Like

  20. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s