Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Rebuilding the SQL Server master database (Part 1 of 3)

By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | More > Disaster Recovery

Attend a SQL Server Conference for FREE >> click to learn more

I recently found myself in a situation where I had to rebuild the master database on one of the nodes of our development SQL 2005 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 DVD.  So, how do you prepare for a master rebuild and what do you do afterwards?  Hopefully this checklist will give you an idea.

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.

Task Steps
Script "Detach of 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'')
SELECT ''EXEC usp_KillDBConnections ''''?''''
EXEC sp_detach_db ''''?'''',''''true''''''

EXEC sp_MSforeachdb 

Save the output of this query as 0.sql

Script "Attach of 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

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

EXEC sp_msforeachdb 

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 2005.)  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:

http://support.microsoft.com/kb/918992 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. 

For the sake of this tip, 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

@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 

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 located here, 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 rebuild 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:
  • 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.

Next Steps

  • At this point you're prepared to rebuild the master database.  The next tip in this series will outline that process and the final tip will walk you through the restoration of databases and objects post-rebuild.
  • sp_MSforeachdb is thoroughly explained in this tip.
  • For information concerning the system databases review this tip.
  • Read Part2, Part3

Last Update:

signup button

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

SQL tips:

*Enter Code refresh code     

Friday, April 27, 2012 - 11:11:00 AM - Salman Back To Top

I don't understand your first step where you are detaching all the user databases. First of all you are trying to rebuild master DB, means that you have something wrong with your master DB. In the absense of master DB SQL Server will not be able to start.

Monday, August 04, 2008 - 3:35:53 PM - pdav221 Back To Top
On script 4 (PROCEDURE sp_SecurCreation), the text recommends just doing the system DBs.
To do that you can swap out this line:
 INSERT INTO #TempSecurables2
 EXEC master.dbo.sp_MSforeachdb @command1='USE [?]
For these lines:
 INSERT INTO #TempSecurables2
 EXEC master.dbo.sp_MSforeachdb @command1='USE [?]; IF db_name() IN (''master'',''msdb'',''ReportServer'',''distribution'',''tempdb'',''model'') BEGIN
and you can swap out these lines (just adding an "END"):
 WHERE GRANTEE <> ''public'''

For these lines: 
 WHERE GRANTEE <> ''public'' END'

Wednesday, July 09, 2008 - 5:59:32 AM - simon_l Back To Top

that seems to work just fine now


thanks you



Tuesday, July 08, 2008 - 12:10:58 PM - grobido Back To Top

I gor rid of the square brackets and it seemed to work:

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

EXEC sp_MSforeachdb @Command_Detach


Tuesday, July 08, 2008 - 6:48:47 AM - simon_l Back To Top

 this is more than likely me .. but .. 

 the automated detach syntax creating query attempts to exclude master etc

and yet  it goes off and creates the following

EXEC usp_KillDBConnections 'master'
EXEC sp_detach_db 'master','true'

 any ideas ?

thanks ~si

Learn more about SQL Server tools