SQLServerCentral / Discuss content posted by Rudy Panigas / Article Discussions / Article Discussions by Author / Rebuild SQL Server Master Databases in Less than 5 Minutes / Latest Posts

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

david.shink — Thu, 18 Aug 2011 11:51:39 GMT

Larry: I am somewhat of a newbie to SQL Server database administration. Your last post is just the sort of thing I would need if I really lost the master database, and users, and my manager were standing over me asking when it will be "fixed". I guess we have all been there. Thank you for taking the time. David Shink

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

larry Hennig — Thu, 18 Aug 2011 11:36:57 GMT

Yes, but do a copy action not a move action, to ensure the destination folder's security settings are applied to the files. The DBA team I am part of has been relying on the method I described for two years of DR exercises and it has always worked. Our daily backup generates fresh copies of the System Database files every day (as soon as the backups are created, we via restore each to a different db name and file location then detach the new db). For a recovery, we no longer restore the System Dbs from backups - we just copy the most recent copies of the files back into the original location(s) and then start up the engine. The only catch we have encountered is that you have to take ownership of the detached files and grant yourself access to them before you can copy them (upon detach, SQL Server reduces the rights to the MDF and LDF files so that only the identify that detached them has any access).:cool:

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

david.shink — Thu, 18 Aug 2011 11:23:55 GMT

This tip/how-to assumes that you can connect to the SQL Server instance. If I lost the master database, being able to connect would be the first thing I would worry about. Maybe you mean that if I move the copies I made before the accident back to replace the damaged master files, then I could connect. I have not had that experience yet. In any case, this looks like a good, solid tip. Thank You, David Shink

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas — Mon, 07 Mar 2011 11:38:09 GMT

Just goes to show how there are many ways to get the same job done. Thanks for your comments,Rudy

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

larry Hennig — Mon, 07 Mar 2011 11:34:21 GMT

Ah, so he did. Clever fellow. :hehe: I will edit the post to remove the "please credit me" comments.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

UMG Developer — Mon, 07 Mar 2011 10:24:50 GMT

[quote][b]larry Hennig (3/4/2011)[/b][hr][size="2"][b]HOW TO [i]ELIMINATE [/i]THE NEED TO REBUILD THE MASTER DATABASE[/b][/size]I have not seen this idea offered by anyone else, so I am claiming credit for the algorithm. :smooooth: If you describe this method anywhere else, please be up front about giving me (Larry Hennig :-)) credit for coming up with the algorithm. Likewise, please put a suitable credit in an in-line comment if you implement this algorithm in code.[/quote]You will notice on page 2 that IAN suggested essentially the same thing of backing up the DB, restoring it, and then detaching it. However, he didn't go into as much detail about how to make the process more reliable/resilient. (see post: [url=http://www.sqlservercentral.com/Forums/FindPost1058073.aspx]here[/url])

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

larry Hennig — Fri, 04 Mar 2011 19:35:27 GMT

(ignore this)

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

larry Hennig — Fri, 04 Mar 2011 19:31:33 GMT

[size="2"][b]HOW TO [i]ELIMINATE [/i]THE NEED TO REBUILD THE MASTER DATABASE[/b][/size]You can create copies of the system DB files [i]while SQL Server is running[/i]. :cool: Enhance your daily backup job such that, after the master database is backed up, a script does this:1. Obtains the name of the backup file or device (from the system tables).2. Restores the backup as "master2", using WITH MOVE to specify the default physical filenames, but in a different location, such as a subfolder to your backups.3. Detaches the master2 database.Those steps will create a ready-to-use copy of the master MDF and LDF files [b]without interrupting the availability of applications[/b] (because there is no need to stop SQL Server). The files will have the same "recovery point" date and time as the BAK file. When replacement system DB files are needed, there is no need to use special startup modes, no need to reinstall or REBUILD, no need for a ghost installation of SQL Server, no concerns about having exact documentation of the instance configuration or patch level. NOTE: When SQL Server detaches a database, [b]the file permissions are reduced such that only the run-time identify of SQL Server will have access to them[/b]. If you need to use the copies, you will have to take ownership of the files first. You should COPY them back into place (instead of MOVING them) to ensure that they inherit the permissions of the folder they belong in.You can also do this to the msdb and model databases, to allow you to very quickly restore all three system databases and get SQL Server running again. A fuller version of this solution would rename the prior copy of the files to *.old until the new copies have been generated, and would start by checking for evidence of a prior failure by checking whether any *.old files exist. With "the backup folder" being a special location for the copies of the Sysdb files, here is the algorithm I am using:1. In the backup folder , check for files named *.mdf.old. If found, raise an alert that the prior run did not end cleanly (include a directory listing) and then continue.2. In the backup folder, delete any files that are named *.mdf.old or *.ldf.old.3. In the backup folder, rename *.mdf to *.mdf.old rename *.ldf to *.ldf.old4. Check whether any *.old files are missing. if so, raise an alert and then continue.5. For master, msdb and model ...5.1 Obtain the name of the current backup files (from the system tables).5.2 Restore the backup as "<dbname>2", using WITH MOVE to allocate it with the default physical filenames, located in the backup folder. 5.3 Detach the <dbname>2 database.6. Check whether any MDF or LDF files are missing. if so, raise an alert and then STOP.7. In the backup folder, delete *.old8. Check whether any *.old files exit. if so, raise an alert and continue.The steps that check for files or the lack of files may seem redundant, but they serve an important role: they ensure that you are alerted to problems even if those problems did not raise an error that caused the job to fail. (Ensuring the ability to restore data is a DBAs primary responsibility, so I like to have a high level of confidence that the file copies were created successfully).This algorithm is also robust enough to handle the situation of a prior run being interrupted.The text of the alerts can be accumulated until the end (or until a STOP is encountered). That would allow all information to be sent in one alert. These copies of the system database file are backed up to tape along with the server, so if the server is restored from an older backup, the copies will also be restored as of that date, so they will match the state of the instance's binaries. I would share my code, but alas, I do not own it. I might write a version of my own to share - perhaps in PowerShell.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

SQLRNNR — Tue, 08 Feb 2011 15:30:53 GMT

Thanks for the article.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

bcb — Fri, 04 Feb 2011 13:20:42 GMT

Hey PCD that's a cool tip, thanks. :-)

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

veena_kedia — Fri, 04 Feb 2011 11:24:34 GMT

We do it every year once, we have very huge prod DB in log shipping. The client wants to get copy of then live prod db for their testing and reports. We copy our master files and logshipping undo tuf file soemwhere else on DR. Restore the LS db in recovery mode and get the backup to restore later with diff name. Then remove just restored DB, and restore the copied files in similar fashion. I thought we were cheating:unsure: but happy to know we are not alone now;-)It is much less time comsuming than getting backup from prod server.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

pcd.1 — Thu, 03 Feb 2011 21:57:38 GMT

I use the following command when I stuff up the collation:start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=Password01 SQLCOLLATION=SQL_Latin1_General_CP1_CI_ASThere are various other switches etc - refer BOL. Yes it does rebuild the master database, this is for when you realise early enough that you have stuffed up. Which I obviously have done enough times to make it worthwhile hanging on to this command :-)pcd

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

bcb — Thu, 03 Feb 2011 16:40:11 GMT

Yup, a useful tip. As an aside, I'm bummed they discontinued rebuildm.exe. I only used it once, but it worked fine. Nobody mentioned, but (I believe since SQL 2005), you can't "rebuild master" anymore. You have to uninstall/reinstall SQL.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

neeraj_nagpal — Thu, 03 Feb 2011 12:27:12 GMT

I don't think I am going to take my prod down to save 15-30 mins on recovery. If you do it right the rebuild master shouldn't take more than that anyway.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

UMG Developer — Thu, 03 Feb 2011 10:29:58 GMT

Thanks for the article, it makes a good point in that in some cases a SQL Server backup isn't enough, and that having an actual copy of the DB files can speed up the recovery of some problems.I like the suggestion that Ian gave in that you restore the backup of your system databases with different names and detach them, that way no down-time is necessary get an up-to-date DR copy of them.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

mohammed moinudheen — Thu, 03 Feb 2011 10:28:28 GMT

[quote][b]Rudy Panigas (2/3/2011)[/b][hr][quote][b]mohammed moinudheen (2/3/2011)[/b][hr]Rudy,The title of this article is really catchy.Regarding this section: [b]It's All Proactive[/b]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.[/quote]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.[/quote]Rudy,Thanks for clarifying.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Markus — Thu, 03 Feb 2011 09:13:45 GMT

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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

nick.welham — Thu, 03 Feb 2011 09:00:59 GMT

Thanks! Will a replacement of the the MSDB database populate the SQL Server Agent jobs?

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas — Thu, 03 Feb 2011 08:55:09 GMT

[quote][b]nick.welham (2/3/2011)[/b][hr]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![/quote]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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

dant12 — Thu, 03 Feb 2011 08:53:14 GMT

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

nick.welham — Thu, 03 Feb 2011 08:48:50 GMT

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!

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas — Thu, 03 Feb 2011 08:40:58 GMT

[quote][b]Markus (2/3/2011)[/b][hr]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.[/quote]Markus, I see you got the point of my article.Thanks :-)

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas — Thu, 03 Feb 2011 08:04:42 GMT

[quote][b]mohammed moinudheen (2/3/2011)[/b][hr]Rudy,The title of this article is really catchy.Regarding this section: [b]It's All Proactive[/b]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.[/quote]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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas — Thu, 03 Feb 2011 08:02:28 GMT

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

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Ian Scarlett — Thu, 03 Feb 2011 07:21:53 GMT

There is a way to do this without downtime for SQL Server.1. Backup the master database2. Restore the backup file as a differently named database3. Detach this databaseYou 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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

MysteryJimbo — Thu, 03 Feb 2011 06:35:54 GMT

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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Markus — Thu, 03 Feb 2011 05:44:07 GMT

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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

dant12 — Thu, 03 Feb 2011 05:10:24 GMT

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?

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

george sibbald — Thu, 03 Feb 2011 03:53:20 GMT

[quote][b]M A Srinivas (2/3/2011)[/b][hr]Practically this is not possible. In production you can not stop and start the service everyday and changes occur to master database every day .[/quote]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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

gwatson 93229 — Thu, 03 Feb 2011 02:25:52 GMT

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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

mister.magoo — Thu, 03 Feb 2011 02:16:29 GMT

Thanks for the article.I personally was disappointed to find that it was not about [b]rebuilding[/b] the master database as the title said it would be though...

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

M A Srinivas — Thu, 03 Feb 2011 01:51:40 GMT

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

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

mohammed moinudheen — Thu, 03 Feb 2011 01:07:27 GMT

Rudy,The title of this article is really catchy.Regarding this section: [b]It's All Proactive[/b]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.

RE: Rebuild SQL Server Master Databases in Less than 5 Minutes

Satnam Singh — Thu, 03 Feb 2011 00:43:43 GMT

Awesome. Wish there could have been more than 5 *Cheers,Satnam

Rebuild SQL Server Master Databases in Less than 5 Minutes

Rudy Panigas — Wed, 02 Feb 2011 21:44:09 GMT

Comments posted to this topic are about the item [B]Rebuild SQL Server Master Databases in Less than 5 Minutes [/B]