Rebuild Master Database in SQL 2005

<< Team Build TF42046: The Build Service used in the build process is not reachable | Home | How to determine backup information in SQL 2005 >>

Rebuild Master Database in SQL 2005

A while ago a friend told me that he was having problems rebuilding the master database in SQL 2005 running on WindowsXP SP2. I wanted to verify the steps to rebuild master when runnnig on Windows XP since I rarely work on XP boxes just to make sure there were no hidden gotchas in the process. Here is what I did to rebuild the master db in the environment described above:

Step 1. Put SQL Server into Single User Mode

The first thing you will want to do is stop the SQL Sever Sevicer (sqlservr.exe) and the associated services (Agent, Full Text, Etc). There are a few ways to do this, but the easiest way would be to use the SQL Configuration Manager (Start->All Programs->Microsoft Sql Server 2005->Configuration Tools->Sql Server Configuration Manager). From this console you can manage the various SQL server services running on the machine. Right Click on each service listed and stop the service. The services are stopped, you can proceed to Step 2.

Step 2. Start the SQL server in Single User Mode

Open a command window and navigate to the folder where Sqlservr.exe resides (generally <install drive>:\Program Files\Microsoft Sql Server\MSSQL.1\MSSQL\Binn). Run “sqlserver.exe -m” to start the sql server from single user mode.

Step 3. Rebuild the system databases

In SQL 2005, the rebuildm.exe program is nto supported. To rebuild the master database you need to use the setup.exe found on the SQL 2005 installation media. To rebuild, use the following command “start /wait setup.exe /qn INSTANCENAME=<instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<New SA Password>”. Please refere to Books on Line for compelete details on how to use Setup.exe.

Step 4. Restart the SQL Server Services in regular mode

End the command shell you started earlier. Ctrl-C to stop SQL server in single user mode, then close the command window. Go back to the Sql Server Configuration Manager and restart the SQL services.

Print posted @ Saturday, December 17, 2005 11:08 AM

Feedback

# re: Rebuild Master Database in SQL 2005

hi,
The clarification provided to rebuild master database is good. but it would have been more effective if other operating systems were also taken into account instead of XP alone.
regards
raj 1/11/2006 7:13 PM | raj

# re: Rebuild Master Database in SQL 2005

thanks for the comment Raj. Basically the rebuild works the same way in Windwos 2003 as well. The reason why I explicitly called out XP is becuase someone specifically said they could not rebuild master on an XP box.

Thanks,
Tom 1/12/2006 5:23 AM | Tom

# re: Rebuild Master Database in SQL 2005

thanks for the clarification Tom. 8/20/2006 3:25 AM | raj

# re: Rebuild Master Database in SQL 2005

But what about to rebuild Master database if you don't have the Installation media for SQL 2005 Express? We have only a ".msi" file, not any setup.exe one. 1/25/2007 4:14 AM | Monomio

# re: Rebuild Master Database in SQL 2005

We encountered the same error with a project we were deploying recently. Months after the customized system was first run using SQL Server 2005 Express, a cashier accidentally unplugged the power cord while transacting, thus the PC was immediately shutted down without having to close windows. Upon restart, the customized system cannot anymore connect to the database and it was displaying an "Access Denied" message. Uon knowing this, we thought that the master databse could have been corrupted. We tried simulating the problem and it seems that SQL Server is supposed to rollback and forward in cases like this. We were not able to replicate the issue. I am wondering if ever you guys know any measures to avoid corruption of master databse or any causes why corruption occurs. Can you also tell me any simple steps or script for rebuilding? Thanks. 3/5/2007 10:35 PM | Ryan Ericson Canlas

# re: Rebuild Master Database in SQL 2005 for error during Visual Studio Team System

i'm trying to instal VSTS but i change my collation of my SQLServer 2005 like it says in the installation guide of VSTS but when i was installing VSTS it shows a error message, that says "VSTS does not support your collations" so i try to rebuild my SQLServer and change my collation but, doig this starts good but at the end of the process appears that my sql_runner_runner.msi it is fail that it is not the correct version, well i upgrade before my SQLServer so i rebuild trying to rollbac that upgrade, but it doesn work, well i still trying to do domething about it, oh i'm working with windows server 2003 sp1.
If some body could help me i'll aprecciate. 9/8/2007 10:55 PM | shavila

# re: Rebuild Master Database in SQL 2005

I follow the same steps and trying to rebuild the master database, I was struck in step 2, I stopped all the services and change the sql server in single user mode. After that I am not able to get the cursor in prompt location. then i opened one more command prompt and do the step 3 with mentioned option. It was run and not provide any error message. then i break the single user mode and start the services. Now i am trying to connect the server using the old password. It was connected with old password. I don't know where the master database has been rebuild or not. can you please clarify the step 2 and step 3 alone.

1/23/2008 4:54 AM | Saravanakumar R

# re: Rebuild Master Database in SQL 2005

"But what about to rebuild Master database if you don't have the Installation media for SQL 2005 Express? We have only a ".msi" file, not any setup.exe one."

I searched everywhere for this too. What I can do is run the msi and it will create a folder in the root with the temporary install files. This has the setup.exe. The temp folder is deleted when you finish/cancel installation so I take a backup of the folder and run it from there.

Unfortunately I get a different error after running the command. Hope it helps someone out there! 4/14/2008 6:57 PM | Gary

# re: Rebuild Master Database in SQL 2005

Actually, just figured it out using this blog entry :)

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/26/772550.aspx

extract using "SQLEXPRESS.EXE -x"

and run making sure you use "REINSTALLMODE=vomus"

Hope that helps! 4/14/2008 8:09 PM | Gary

# re: Rebuild Master Database in SQL 2005

Hi,
I did as per the above from the d:\setup folder of the 2005 installation cd and got this error:
Microsoft office 2003 Web Components Setup
error 2711 an internal error has occurred SQL_Engine

This is the command I ran to change the colation sequence
start /wait d:\setup\setup.exe /qb INSTANCENAME=<Instance_Name> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<password> SQLCOLLATION=SQL_Latin1_General_CI_AS

9/18/2008 7:58 AM | Jeffr

# re: Rebuild Master Database in SQL 2005

I also unsinatlled the Microsoft Oddice Web components but that didnt overcome the error message 9/20/2008 4:06 AM | Jeffr

# re: Rebuild Master Database in SQL 2005

Hi Tom,

Its a very gud article. I am fine with steps 1 and 2. Could you please elaborate step 3 please.
10/23/2008 2:22 PM | ravi

# re: Rebuild Master Database in SQL 2005

When the system databases are corrupted, how can you start SQL Server in single user mode (Step - 1)? 2/8/2009 9:53 PM | Pravat Kumar Pala

# re: Rebuild Master Database in SQL 2005

Thanks Tom!
Your post really saved us some grief.... 2/17/2009 8:17 AM | clint

# re: Rebuild Master Database in SQL 2005

Good post Tom.

Keep it up. 2/23/2009 5:52 AM | Sachin

# re: Rebuild Master Database in SQL 2005

I have found a entry for collation under hkey_local_machine. what will happen if i change the value to the collation that i want it to have.

Thanks
Dinesh 3/11/2009 6:10 AM | Dinesh

# re: Rebuild Master Database in SQL 2005

very good post..

--seshu 6/24/2009 8:23 AM | seshu

# re: Rebuild Master Database in SQL 2005

Excellent article Tom..... 7/3/2009 8:59 AM | TS

# re: Rebuild Master Database in SQL 2005

Typo: Step 2 should be “sqlservr.exe -m” 9/23/2009 5:45 AM | James

# re: Rebuild Master Database in SQL 2005

Thanks.
It's a good job there are people creating this no-nonsense documentation as the Microsoft stuff is dreadful!
They never bother to update it and we all end up using Google.

Just a note that you'll lose your logins you've created and may need to do somthing like the following against the databases that use them after adding them back in:
sp_change_users_login 'Update_One', '<login>', '<login>' 9/23/2009 5:50 AM | James

# re: Rebuild Master Database in SQL 2005

wanted to verify the steps to rebuild master when runnnig on Windows XP since I rarely work on XP boxes just to make sure there were no hidden gotchas in the process. Here is what I did to rebuild the master db in the environment described above: 10/20/2009 10:03 AM | nike shoes new

# re: Rebuild Master Database in SQL 2005

very good post 11/8/2009 11:25 PM | jim

Tom Wisnowski