<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Rudy Panigas
»
Rebuild SQL Server Master Databases in Less...
35 posts, Page 2 of 4
««
1
2
3
4
»
»»
Rebuild SQL Server Master Databases in Less than 5 Minutes
Rate Topic
Display Mode
Topic Options
Author
Message
Ian Scarlett
Ian Scarlett
Posted Thursday, February 03, 2011 7:21 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 8:44 AM
Points: 1,322,
Visits: 4,399
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
Rudy Panigas
Rudy Panigas
Posted Thursday, February 03, 2011 8:02 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 306,
Visits: 1,013
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
Rudy Panigas
Rudy Panigas
Posted Thursday, February 03, 2011 8:04 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 306,
Visits: 1,013
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
Rudy Panigas
Rudy Panigas
Posted Thursday, February 03, 2011 8:40 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 306,
Visits: 1,013
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
nick.welham
nick.welham
Posted Thursday, February 03, 2011 8:48 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 10, 2013 12:21 PM
Points: 7,
Visits: 73
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
dant12
dant12
Posted Thursday, February 03, 2011 8:53 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:19 PM
Points: 242,
Visits: 656
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
Rudy Panigas
Rudy Panigas
Posted Thursday, February 03, 2011 8:55 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 5:32 PM
Points: 306,
Visits: 1,013
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
nick.welham
nick.welham
Posted Thursday, February 03, 2011 9:00 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 10, 2013 12:21 PM
Points: 7,
Visits: 73
Thanks! Will a replacement of the the MSDB database populate the SQL Server Agent jobs?
Post #1058156
Markus
Markus
Posted Thursday, February 03, 2011 9:13 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 8:37 AM
Points: 1,046,
Visits: 2,208
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
mohammed moinudheen
mohammed moinudheen
Posted Thursday, February 03, 2011 10:28 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:34 PM
Points: 2,170,
Visits: 3,582
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 »
35 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
