Rebuild SQL Server Master Databases in Less than 5 Minutes

  • Comments posted to this topic are about the item Rebuild SQL Server Master Databases in Less than 5 Minutes

    Rudy

  • Awesome. Wish there could have been more than 5 *

    Cheers,

    Satnam

  • 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

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

  • 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);

  • 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]

  • 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.

  • 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.

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

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • 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

  • 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

  • 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!

  • Viewing 15 posts - 1 through 15 (of 34 total)

    You must be logged in to reply to this topic. Login to reply