Rebuild SQL Server Master Database

Problem

I recently found myself in a situation where I had to rebuild the master database on one of the nodes of our development SQL Server cluster.  I had to do so because there was a server collation requirement by one of the databases we were looking to host on the server.  The collation change would not cause issues with any of the current databases on the instance so I could safely change the server collation for the instance. 

The process itself is quite simple, the proof is in the preparation however.  The chances things will go wrong are low, but you still need to be prepared for the possibility that your quick rebuild is anything but.  The need to prepare is also critical because once you rebuild the master database you’ll notice that there is a little issue involving NO LOGINS!  Yikes!  Yes, the rebuild gives you a new master database fresh from the media.  So, how do you prepare for a master rebuild and what do you do afterwards?  Hopefully this checklist will give you an idea.

Solution

As mentioned above, the below steps are the process for getting ready for a rebuild of the master database.  In each of these steps I will show you scripts that can be run to collect the data needed to quickly rebuild your server after you have restored the master database.  In future tips we will walk though the actual rebuild process, but for now we will focus on having the necessary data on hand to do the rebuild.

Getting Ready

The biggest thing here is having the necessary data on hand and ready to apply once the master database has been rebuilt.  Without the data below you will need to do a lot of manual work to get your server up and running again.  So in each of these steps we look at what data you will need and how you can script it out from your server prior to doing a rebuild.

Prior to running these change the query output to Text (Ctrl-T), so you can easily save the output.

Script to Detach All User Databases

Before rebuilding master you’ll detach all of the user database on the instance.  This script will
provide output for doing so.

DECLARE @Command_Detach VARCHAR(1000)
SELECT @Command_Detach = '
IF N''[?]'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
SELECT ''EXEC usp_KillDBConnections ''''?''''
EXEC sp_detach_db ''''?'''',''''true''''''
END'
EXEC sp_MSforeachdb @Command_Detach  

Save the output of this query as 0.sql

Script to Attach All User Databases

After rebuilding the master database, you’ll need to attach all the user databases you previously detached. You’ll also notice that when you attach the databases the “dbo” alias will default to the login the script is executed as.  Therefore, you’ll need to not only script out the attach process, but also an ownership change. Script Attach of all user databases as follows, saving the results as 1.sql

DECLARE @Command_Attach VARCHAR(1000)
SELECT @Command_Attach = ' IF N''[?]'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
   SELECT ''EXEC sp_attach_db ''''?'''',
   '''''' + RTRIM(filename) + '''''',''
   FROM [?]..sysfiles
   WHERE fileid = (SELECT MIN(fileid) FROM [?]..sysfiles)
   UNION ALL
   SELECT '''''''' + RTRIM(filename) + ''''''''
   FROM [?]..sysfiles
   WHERE fileid > (SELECT MIN(fileid) FROM [?]..sysfiles) 
   AND fileid < (SELECT MAX(fileid) FROM [?]..sysfiles)
   UNION ALL
 
   SELECT '''''''' + RTRIM(filename) + ''''''''
   FROM [?]..sysfiles
   WHERE fileid = (SELECT MAX(fileid) FROM [?]..sysfiles)
END'
EXEC sp_msforeachdb @Command_Attach  

Script All Logins

The master database is the logical repository for all system objects in a SQL instance. The physical repository being the resource database in SQL Server. When the master database is rebuilt, any changes made to it and data placed into it since installation, will be lost. These items will need to
be replaced after the rebuild process. The most critical of these item types are the instance’s logins. The following scripts will provide you with the means to restore all of your logins after the rebuild process.

This contains scripts for the creation of two stored procedures: sp_hexadecimal and sp_help_revlogin. Download the scripts, run them in the master database to create the stored procedures, then run the following script that utilizes those stored procedures to script out all the logins for every database on your SQL instance:

EXEC dbo.sp_help_revlogin

Make sure you change the output for the query to either text or to file, so you’ll be able to run this script after the rebuild.

Name the file 2.sql.

Script DBO Aliasing for User Databases

Save the results of the following script that will re-establish “dbo” aliasing as 3.sql.

DECLARE @Command_dbo VARCHAR(1000)
SELECT @Command_dbo = 'use [?]
DECLARE @Owners TABLE (dbid int, dbname varchar(100), loginname varchar(100))
INSERT INTO @Owners (dbid, dbname, loginname)
SELECT DB_ID(), DB_NAME(), SL.[name]
FROM sysusers SU LEFT JOIN master..syslogins SL ON SU.sid = SL.sid
WHERE SU.[name] = ''dbo''
SELECT ''EXEC [?].dbo.sp_changedbowner N'''''' + loginname + '''''', '' + ''false''
FROM @Owners WHERE dbid = DB_ID()'
EXEC sys.sp_MSforeachdb @Command_dbo

Script All Securables

Any securables granted at the instance level is necessary because that information is also stored within the master database. Database securables will be retained in the databases themselves. I found the code, created by Pedro Lopes perfect for this task at hand. My suggestion is to limit the results to only the system databases, as the user databases will be reattached with their securables intact.

Save the output of this script as 4.sql.

Script Custom Objects in master and msdb Databases

Quite often we DBAs find ourselves creating objects such as the sp_hexadecimal and sp_help_revlogin stored procedures we created earlier in the system databases.  Be sure that if you wish to recreate these objects after rebuilding that you follow the same process outlined above for each object (script as create to file.)

Script Remaining Objects

For each of the following object types you wish to recreate after the rebuild of the master database (System Objects, SQL Agent Jobs, Operators) perform the following steps:

  • In SQL Server Management Studio, select object in Object Explorer
  • Right click and select Script as CREATE
  • Select To File
  • Take note of the file name for restoration process after rebuild

Without the above information ready to be applied after the master database has been rebuilt you would need to go through a lot of manual work to get your server ready for users.  Although this was an exercise in how to pull the data together, these steps should be built into your disaster recovery steps so you always have the necessary data on in hand in case of the need to restore the master database.

Detach all User Databases

Using the script saved as 0.sql from above, you will first need to detach all user databases. 

When the master database is rebuilt, it has no metadata that corresponds to any user databases or objects on the instance.  By detaching the user databases before the rebuild it assures a clean rebuild and removes any chance of contention on the files just in case something goes awry in the rebuild process. 

Execute 0.sql.

Load Media

Load the necessary media that corresponds to the edition and platform for the SQL instance that you’re preparing to rebuild. 

Open Command Prompt and Initiate Rebuild Process

Open command prompt then run the following command, substituting the required values for your instance:

start /wait <path>\setup.exe [/qb | /qn] {VS=<VSName>} INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=<Current_Domain_User_Password> SAPWD=<New_Password> SQLCOLLATION=<NewSystemCollation>

  • <path> = path where the SQL Server setup.exe file is located
  • [/qb or /qn] switch = /qb does not suppress errors and notification from the workstation during the rebuild process.  /qn will suppress these messages.  I prefer to know what is occurring with the processes I am running and I recommend this world view to all other DBAs no matter how informed and proficient they are, therefore you’ll see I choose /qb for the switch in my sample script below.
  • VS = If this is a clustered instance then you’ll need to include the VS (Virtual Server) parameter which is the name of the virtual server for the instance being rebuilt.  This parameter can be omitted on a non-clustered instance of SQL Server.
  • INSTANCENAME = Instance name for SQL Server if the target instance is a named instance.  Can be omitted if the instance being rebuilt is a default instance of SQL Server.
  • REINSTALL = This parameter controls what specific sub processes are kicked-off by setup.exe.  Since this series concerns rebuilding to change SQL instance collation we’re specifying the parameter value of SQL_Engine REBUILDDATABASE=1
  • ADMINPASSWORD = Parameter that corresponds to the currently-logged in domain user that is running this rebuild process.  The user must be a member of the SQL instance’s sysadmin server role.
  • SAPWD = Password for the sa SQL login once the master database is rebuilt.  As always, a strong password is suggested.
  • SQLCOLLATION = Since this series is concerned with changing the collation of an existing SQL instance, we’re specifically interested in this parameter.  Specify the new collation here.

Examples

  • General Example Specifications:
    • Current login DOMAIN\DBA1 is sysadmin role member with pwd of ‘DBA1pwd’
    • Path for setup.exe = C:\temp\MSSQL.1
    • New ‘sa’ password to be ‘!grac3undrpre$$ure!’
    • New SQL collation to be ‘SQL_LATIN1_GENERAL_CP1_CI_AS’

SQL Server Cluster Example

Rebuild clustered instance of SQL Server with the following specifications:

  • Virtual Server name = CLUST1
  • Instance name = NODE1
start /wait C:\temp\MSSQL.1\setup.exe /qb VS=CLUST1 INSTANCENAME=NODE1 REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=DBA1pwd SAPWD=!grac3undrpre$$ure! SQLCOLLATION=SQL_LATIN1_GENERAL_CP1_CI_AS

SQL Server Standalone Named Instance Example

Rebuild non-clustered, named instance of SQL Server with the following specifications:

  • Instance name = NODE1
start /wait C:\temp\MSSQL.1\setup.exe /qb INSTANCENAME=NODE1 REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=DBA1pwd SAPWD=!grac3undrpre$$ure! SQLCOLLATION=SQL_LATIN1_GENERAL_CP1_CI_AS

SQL Server Standalone Default Instance Example

Rebuild non-clustered default instance of SQL Server:

start /wait C:\temp\MSSQL.1\setup.exe /qb REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=DBA1pwd SAPWD=!grac3undrpre$$ure! SQLCOLLATION=SQL_LATIN1_GENERAL_CP1_CI_AS

Backup System Databases

Once the rebuild is complete, take the time to backup the master, model, and msdb databases.  I strongly suggest not overwriting the previous backups for these databases from prior to the rebuild until you’re absolutely sure that the rebuild was successful.

Apply All Patches and Security Updates

The rebuild process takes your system databases back to the RTM (Release-To-Manufacturing) patch set level.  Take the time now, to apply all necessary service packs and security patches to bring you back to the level you were at prior to the rebuild process.

Backup System Databases Again

Some call it overkill, I call it being a DBA. Backup those system databases once again now that they are at the patch level that corresponded to the level pre-rebuild.

Attach All User Databases

Now we finally move on to what matters most to the users – their databases.  In our ramp-up to rebuild we created a script called 1.sql.  Review it once again before running it – make sure that the master, msdb, model, and tempdb databases are not included, and then run it.  Once completed, you’re instance will be almost to the point where it needs to be, but you’re not quite there yet!

Recreate Logins

In the first tip we ran a script where the output was a series of CREATE LOGIN… T-SQL commands that we saved as 2.sql.  Well now is the time to load 2.sql and execute it.  The end results are for the most part still going to be unusable at this point for any login whose default database is not set to a system database (since we’ve yet to attach any of the user databases).  There is no need for concern since we’re not anticipating users attempting to connect to the instance yet.

Restore Database dbo Aliases

Run the 3.sql script created above to restore dbo aliases in all your user databases.

Create Custom Objects in master and msdb Databases

It may seem out of order, but we’re now going to run any scripts that were created during the last step we undertook in preparation for the rebuild.  In that step we scripted out any custom objects that existed in the system databases.  Since those are the only databases that now exist in our instance, we can safely re-run the scripts to re-create them.

Create SQL Agent Jobs, Operators, etc.

Continuing the theme of working out of order, you’re now going to run any scripts you created previously that pertained to SQL Agent objects:  Jobs and Operators in particular.  You may note that Job Schedules were not included in this list.  That is because those objects are created within the Job creation script if they do not already exist in the msdb database.

Restore System Databases’ Securables

When the user databases are restored any securables are restored along with them.  This is not the case with the system databases however.  These databases are completely rebuilt and as noted previously, need to have some post-build work done to ensure they are in the same state they were in previously.  Verify that 4.sql contains only the securables for the system databases and edit it for only those securables that you want to re-apply.  (Now is a great time for cleanup!)  When 4.sql is in the state you wish, execute it.

Review and Correct SQL Instance Service Accounts

Before turning the SQL instance back over to the users take the time to review the instance service accounts for not only the SQL Server service, but also the agent and browser services.  Make changes accordingly and restart the services if necessary.  It’s best to do this now rather then discover an inconsistency or problem later when users would be impacted unnecessarily.

Next Steps

  • sp_MSforeachdb is thoroughly explained in this tip.
  • For information concerning the system databases review this tip.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *