Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Rebuild SQL Server Master Databases in Less than 5 Minutes Expand / Collapse
Author
Message
Posted Thursday, February 3, 2011 10:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #1058221
Posted Thursday, February 3, 2011 12:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:52 AM
Points: 5, 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.
Post #1058299
Posted Thursday, February 3, 2011 4:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:10 PM
Points: 21, Visits: 103
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.


Post #1058459
Posted Thursday, February 3, 2011 9:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 6, 2012 11:24 PM
Points: 85, 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
Post #1058502
Posted Friday, February 4, 2011 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:34 AM
Points: 6, 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 cheating but happy to know we are not alone now

It is much less time comsuming than getting backup from prod server.
Post #1058917
Posted Friday, February 4, 2011 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:10 PM
Points: 21, Visits: 103
Hey PCD that's a cool tip, thanks.


Post #1058997
Posted Tuesday, February 8, 2011 3:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1060708
Posted Friday, March 4, 2011 7:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:46 PM
Points: 58, Visits: 244
HOW TO ELIMINATE THE NEED TO REBUILD THE MASTER DATABASE

You can create copies of the system DB files while SQL Server is running.

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.
Post #1073641
Posted Friday, March 4, 2011 7:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:46 PM
Points: 58, Visits: 244
(ignore this)
Post #1073642
Posted Monday, March 7, 2011 10:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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. 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)
Post #1074306
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse