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

By:   |   Updated: 2008-06-26   |   Comments (3)   |   Related: 1 | 2 | 3 | > Disaster Recovery


Problem

Previously in this series about how to rebuild the master database, we itemized the steps required to prepare for rebuilding the master database.  In the third tip in the series will present how to restore your SQL instance back to the state it was in prior to the rebuild process.  In this tip, it presents what is involved in destroying your instance which is really what a rebuild does, believe it or not.  I know that is a fairly-severe description of what you're about to do, but this is what it feels like when you're just about to hit the F5 key that executes that first query that detaches all your user databases:  "WHAT AM I DOING!?" 

Hopefully this tip takes some of the fear out of what you're about to do.

Solution

Before getting started there are a few prerequisites that you will need.

  1. Scripts that were created from the first tip in this series
  2. The media that was used to install SQL Server the first time
Task Steps
Detach all User Databases Using the script saved as 0.sql from the first tip in this series, 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.  This can be loaded directly into the DVD drive of your workstation/server, or copied locally from a file share somewhere on your domain.
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 2005 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'

Example 1:  Rebuild clustered instance of SQL Server 2005 with the following specifications:

  • Virtual Server name = CLUST1
  • Instance name = NODE1

c:\>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

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

  • Instance name = NODE1

c:\>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

Example 3:  Rebuild non-clustered default instance of SQL Server 2005:

c:\>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 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.
Follow Process in Final Tip in Series to Re-Establish All Instance Specific Content The final tip in this series will step you through the execution of all the scripts created in Part 1 against the rebuilt instance.  The end result is your instance returned to you in a pre-rebuild state only with the server collation you needed originally.
Next Steps
  • The final tip in this series will walk you through the steps necessary to re-attach your user databases and recreate all user-based content that existed prior to the rebuild.  This is the post-rebuild process in a nutshell.
  • Read Part1, Part3


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-06-26

Comments For This Article




Thursday, April 5, 2012 - 8:34:45 AM - moenier Back To Top (16790)

Hi

Would this apply to SQL 2008 R2 as well?

Saturday, September 17, 2011 - 1:19:56 AM - TK Back To Top (14682)

Awesome article.Many Thanks!!!

Tuesday, December 1, 2009 - 7:07:51 AM - FreeHansje Back To Top (4502)

 Hi Tim,

I'm struggling with exactly the situation I you describe in this 3-part series, so I was glad to have found your article. However, when running the script per your example(I had forgotten to  enter the ADMINPASSWORD) I receive the an error on a property not specified: GROUP. Looking into BOL I see this should only be entered when executing a new installation, while I am rebuilding the master in order to change the collation. After entering this group-parm I run into another error, the node where to install not given... I thought this should be the VS-parm, but alas.

I look further into this, maybe you wish to add to your article on my findings. BTW, I am working on e W2008 with SQL2005 cluster.

Cheers,

FreeHansje















get free sql tips
agree to terms