SQL SERVER – Reasons to Backup Master Database – Why Should Master Database Backedup

The most interesting thing about writing blog at SQLAuthority.com is follow up question. Just a day before I wrote article about SQL SERVER – Restore Master Database – An Easy Solution, right following it, I received email from user requesting reason for importance of backing up master database.

Master database contains all the system level information of server. Information about all the login account, system configurations and information required to access all the other database are stored in master database. If master database is damaged, it will be difficult to use any other database in SQL Server and that makes it most important database of the SQL Server.

Let us understand the important of the master database using an example. We will take example of SQL Server DBA and follow his timeline. Make sure to understand it correctly, as I have small question at the end of the timeline.

9:00 AM – DBA takes backup of the master database.
10:00 AM – DBA creates new Database named AfterMaster.
11:00 AM – DBA restores the master database backup taken at 9:00 AM.
12:00 PM – I have following two questions for the DBA :

Question 1) What will be the state of the database AfterMaster? If AfterMaster database will be in active state after restoring master database?
Question 2) What should be the next step after restoring master database?

Let us understand the answer of question.

Answer 1) Once master database is restored it will have no record of AfterMaster database in its system database and it will not recognize it.
Answer 2) If master database is restored from backup all the operation which are done after last master database backup should be repeated in order to bring SQL Server in the current operational state. In our case, the database files (ldf and mdf) of AfterMaster database will still exists on server. They should be reattached to the server. You can search about how to attach mdf and ldf file at Search@SQLAuthority.com.

It is clear from our example that master database contains user login, files, filegroups and server wide settings.

In summary, it is extremely important to take backup of the master database.

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

About these ads

5 thoughts on “SQL SERVER – Reasons to Backup Master Database – Why Should Master Database Backedup

  1. how frequently should we do this master db backup,
    is it after certain operations [like DDL commands] or just after
    any regular db backups ???

    thanks in advance.

  2. I do ofline data entry of nrega scheme. We used sql. My problem is when i upload the financial statment and after uploading the figures are not reflected uptodate. Later master backup was restored but the problem still persist. Can anyone help?

    • maheshbabuummaneni,
      Did you ever get a response or learn about what to do with the other databases?
      I’m restoring to a cold site server, but there’s not a lot of information about the restoring of the other databases. I would think that you would want to, but when I read articles about restoring the master, there is no mention of the other databases.
      Thank you

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

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