Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Rebuild SQL Server Master Databases in Less than 5 Minutes Expand / Collapse
Author
Message
Posted Thursday, February 3, 2011 7:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:22 AM
Points: 1,327, Visits: 4,506
There is a way to do this without downtime for SQL Server.

1. Backup the master database
2. Restore the backup file as a differently named database
3. Detach this database

You now have the mdf and ldf files for the master database that can be used in the same way the article suggests, but without any downtime.

This could even be done as a scheduled job, so you have an up to date master backup that can be easily restored any time.



Post #1058073
Posted Thursday, February 3, 2011 8:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 312, Visits: 1,102
Hello everyone,

Thanks for all your comments. Looks like some people like and dislike the article but this is to be expected.

Apparently some are commenting that the article name is misleading. Actually it is not. The purpose of the article is to help get the master database back up and running. Replacing the master database with a working "copy" is the same as rebuilding it. Technically you are replacing the database but the end results are the same. So if you restore it, copy it or rebuild the master database who cares as long as the business can get to their data. Without the business DBAs would be out of work.

Others comment on the fact the this can not be done everyday. Well, you don't have to. It's all up to you how you want the run your environment. My "Real World Example" is how I use this process. It's for our disaster recovery site. I also run this process on our production servers when we do patching on the OS and/or SQL server. Since SQL server services will be interrupted during our maintenance window I execute a batch file that does the complete process in under 5 minutes.

If you can see the benefit of this process and can use it great.

If you don't like the article, maybe you can post your improved process other than reading off the BOL on how to rebuild your master database.

Hopefully you will get inspired and write your our process/tip and post it on this great website.

Rudy






Post #1058101
Posted Thursday, February 3, 2011 8:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 312, Visits: 1,102
mohammed moinudheen (2/3/2011)
Rudy,
The title of this article is really catchy.

Regarding this section: It's All Proactive
You are referring to a SQL Server instance which is running fine right?
I am assuming you are not copying from the instance which has db corruption.


Yes Mohammed, only IF the instance (default or named) is running fine. Do not run this on a bad/corrupted master database as this will not help you.



Post #1058104
Posted Thursday, February 3, 2011 8:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 312, Visits: 1,102
Markus (2/3/2011)
I try and do this after a patch or when I know I have an outage window. It is always good to have an easy way to get SQL Server back to a somewhat stable state.

To all of those that are disappointed in this article.... All this article is pointing out is if master gets corrupted for any reason this is an easy way to get you back to a stable running SQL Server. That is all. It is not meant to be a daily occurance as no one can stop/start SQL Server on a daily basis in a production environment.



Markus, I see you got the point of my article.

Thanks



Post #1058135
Posted Thursday, February 3, 2011 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:19 AM
Points: 7, Visits: 79
Two questions, if someone could help me please!
1. Does the Master DB contain the server Master encryption key data or should I add this separately?

2. I know the Master db contains the code for the scheduled agents, but if I follow the rebuild sequence outlined will they appear in the SQL Server Agent Jobs automatically, or do I need to dig the code out for each step and recreate it?

Thank you!
Post #1058144
Posted Thursday, February 3, 2011 8:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:18 PM
Points: 242, Visits: 678
nick.welham (2/3/2011)
Two questions, if someone could help me please!
1. Does the Master DB contain the server Master encryption key data or should I add this separately?

2. I know the Master db contains the code for the scheduled agents, but if I follow the rebuild sequence outlined will they appear in the SQL Server Agent Jobs automatically, or do I need to dig the code out for each step and recreate it?

Thank you!

job info is actually in the msdb database


--
Thiago Dantas
@DantHimself
Post #1058150
Posted Thursday, February 3, 2011 8:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 312, Visits: 1,102
nick.welham (2/3/2011)
Two questions, if someone could help me please!
1. Does the Master DB contain the server Master encryption key data or should I add this separately?

2. I know the Master db contains the code for the scheduled agents, but if I follow the rebuild sequence outlined will they appear in the SQL Server Agent Jobs automatically, or do I need to dig the code out for each step and recreate it?

Thank you!


Welham,

Encryption key data should always be backed up on their own regardless of where it lives.

Also the jobs are contained in the MSDB database.



Post #1058152
Posted Thursday, February 3, 2011 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:19 AM
Points: 7, Visits: 79
Thanks! Will a replacement of the the MSDB database populate the SQL Server Agent jobs?
Post #1058156
Posted Thursday, February 3, 2011 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:59 AM
Points: 1,266, Visits: 2,777
Yes. Restoring MSDB database will get you all of your SQLAgent jobs. Keep in mind if you attempt to restore MSDB the SQL ServerAgent service has to be stopped first.

If you are restoring a MSDB database from one server to another and both servers have different names there is some tweaking to change the servername in one of the tables you will have to do.



Post #1058172
Posted Thursday, February 3, 2011 10:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:16 PM
Points: 2,270, Visits: 3,783
Rudy Panigas (2/3/2011)
mohammed moinudheen (2/3/2011)
Rudy,
The title of this article is really catchy.

Regarding this section: It's All Proactive
You are referring to a SQL Server instance which is running fine right?
I am assuming you are not copying from the instance which has db corruption.


Yes Mohammed, only IF the instance (default or named) is running fine. Do not run this on a bad/corrupted master database as this will not help you.


Rudy,
Thanks for clarifying.


Mohammed Moinudheen
Post #1058219
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse