SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild SQL Server Master Databases in Less than 5 Minutes


Rebuild SQL Server Master Databases in Less than 5 Minutes

Author
Message
Ian Scarlett
Ian Scarlett
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5466 Visits: 7144
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.



Rudy Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 1322
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



Rudy Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 1322
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 Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 1322
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 :-)



nick.welham
nick.welham
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 86
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!
dant12
dant12
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 711
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
Rudy Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 1322
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.



nick.welham
nick.welham
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 86
Thanks! Will a replacement of the the MSDB database populate the SQL Server Agent jobs?
Summer90
Summer90
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7423 Visits: 3831
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.
M&M
M&M
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6219 Visits: 3913
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.

M&M
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search