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
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 2204
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.
neeraj_nagpal
neeraj_nagpal
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 74
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.
bcb
bcb
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

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



pcd.1
pcd.1
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 209
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_AS

There 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
veena_kedia
veena_kedia
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 200
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 cheatingUnsure but happy to know we are not alone now;-)

It is much less time comsuming than getting backup from prod server.
bcb
bcb
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 107
Hey PCD that's a cool tip, thanks. :-)



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66967 Visits: 18570
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

larry Hennig
larry Hennig
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 267
HOW TO ELIMINATE THE NEED TO REBUILD THE MASTER DATABASE

You can create copies of the system DB files while SQL Server is running. 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 without interrupting the availability of applications (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, the file permissions are reduced such that only the run-time identify of SQL Server will have access to them. 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.old

4. 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 *.old

8. 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.
larry Hennig
larry Hennig
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 267
(ignore this)
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 2204
larry Hennig (3/4/2011)
HOW TO ELIMINATE THE NEED TO REBUILD THE MASTER DATABASE

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.


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: here)
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