Rebuild SQL Server Master Databases in Less than 5 Minutes

Question

Post reply

Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas

SSChampion

Points: 10702
More actions
February 2, 2011 at 9:44 pm

#246216

Comments posted to this topic are about the item Rebuild SQL Server Master Databases in Less than 5 Minutes
Rudy
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 1

Awesome. Wish there could have been more than 5 *

Cheers,

Satnam

M&M SSC-Insane Points: 21700 More actions · Answer 2

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.

M&M

M A Srinivas SSC-Addicted Points: 494 More actions · Answer 3

Practically this is not possible. In production you can not stop and start the service everyday and changes occur to master datbase every day .

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Thanks for the article.

I personally was disappointed to find that it was not about rebuilding the master database as the title said it would be though...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

gwatson 93229 Valued Member Points: 50 More actions · Answer 5

I was also disappointed. "Rebuilding" to me does not mean "restore from a file I copied earlier". In fact, thinking about it more, it would seem in this case that you had two backups, one on disk locally and one on tape. This is a great idea for disaster recover situations but had the restore from tape worked this whole secondary method would not be needed. In either case, tape or disk, we have not rebuilt the master database, we have restored it from backup.

I am happy to be corrected but I find the title of this article very misleading.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

M A Srinivas (2/3/2011)
Practically this is not possible. In production you can not stop and start the service everyday and changes occur to master database every day .

you would not do this every day. you only need to do it when you apply a patch to SQL, and then there would be an outage anyway. I would also copy the msdb,model and importantly the resource database. The master database is bought up to date by restoring the latest backup as described in the process. BTW SQL needs to be started in single user mode to restore the master database.

This is a sensible addition to your DR strategy.

---------------------------------------------------------------------

dant12 SSCrazy Points: 2712 More actions · Answer 7

i dunno, but this kinda sounds like a bad idea.

i have previously rebuilded the master database to change server collation and it went through painlessly in no time.

anything that would keep you from actually rebuilding the master database in case of corruption?

--
Thiago Dantas
@DantHimself

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 8

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.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 9

This will only apply in a small number of situations. The point of SQL Server providing high availabilty means this is not usually possible without a maintenance slot.

A useful tip rather than a "how to" in my opinion.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 10

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 SSChampion Points: 10702 More actions · Answer 11

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 SSChampion Points: 10702 More actions · Answer 12

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 13

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 🙂

Rudy

Luckydog73 SSC Enthusiast Points: 117 More actions · Answer 14

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!