Reinstalling system databases in SQL Server using setup.exe

Having worked with SQL Server for several years, it’s surprising that I’ve never encountered this particular problem before (a corruption of one of the SQL Server system databases).

I recently came across an instance of SQL Server 2005 that wouldn’t start. After examining the event log, it became apparent that the log file for the model database had become corrupted.

Unlike a corruption in any other database, the principle problem here is that without the model database (or any other of the system databases), it isn’t possible to start SQL Server. Without SQL Server running, it isn’t possible to do a RESTORE DATABASE even if a valid backup were available. 

Whilst trying to resolve this problem, I discovered a method of repairing SQL Server using the setup.exe file. The command to do is below and more details can be found here http://msdn.microsoft.com/en-us/library/ms144259%28SQL.90%29.aspx

start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

The Instancename=MSSQLServer entry is a reference to the default instance. You can also change the collation of SQLServer by using the above command.

I attempted to use this method to reinstall the model database but it failed. The command executed without any errors and none of the system databases were reinstalled. After investigating this further, I discovered that running setup.exe resulted in a WMI configuration error.

This error was resolved by running this the following script found here http://social.msdn.microsoft.com/forums/en-US/vstsstart/thread/8c5d3ffb-bd6d-4210-9bbe-b2f3ddeca50b/

@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End
:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer
:SkipSrv
goto End
:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End

In the end, it’s taken me a fairly long time to do something which should be have been quite simple. However, it’s been useful to have discovered some of the more obscure options of setup.exe along the way.

Advertisements

About dotnettim

Tim Leung is a Microsoft .Net / SQL Server developer based in England.
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s