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/
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
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
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
if not exist wmicore.exe goto End
net start winmgmt
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.