Mikael Wedham

The master.mdf is corrupt. The disk failed… Powerfailure? This never occurs at a convenient time. The ERRORLOG-file has a message similar to this.

Error 2(failed to retrieve text for this error. Reason: 15100) occurred while opening file
‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf’
to obtain configuration information at startup.

This is of course easily fixed. It’s only to resore the backup we have of all our system databases. Just one small problem… SQL Server doesn’t start.

Time to rebuild the system databases. But first : Documentation!

1. Look for “Authentication mode is MIXED.” (or “Authentication mode is WINDOWS-ONLY.” ) Write it up.
2. Document what Collation you want. If you want “same as before”, just forget it. Otherwise write the new Collation on the same piece of paper. (I’m documenting “Finnish_Swedish_CI_AS”)
3. How do you connect to SQL Server? If you have a backslash somewhere in your servername, you have a named instance. (connecting to COMPUTER\FINANCE gives you the instancename FINANCE) However if you only use the servername, you probanly have a default instance, in which case the instancename is MSSQLSERVER – Onto the paper!

Command:

Same syntax since SQL Server 2008. Still works in SQL Server 2014 (CTP2) and has no signs that it would stop working:

Setup.exe /ACTION=REBUILDDATABASE
/INSTANCENAME=MSSQLSERVER  
/SQLSYSADMINACCOUNTS=win7x64pro\mikael 
/SAPWD=P@ssw0rd
/SQLCOLLATION=Finnish_Swedish_CI_AS

• ACTION – obviously
• INSTANCENAME – the instancename documented in item 3.
• SQLSYSADMINACCOUNTS – Windows account(s) who will be sysadmin of the server.
• SAPWD – This parameter MUST be present if item 1 states the server started with MIXED autentication.
• SQLCOLLATION – from item 2.
• QUIET – I recommend using this. Saves some clicking.

OK, now you’ll have to run this command in an elevated command prompt. That is the command prompt starting in C:\Windows\System32 instead of in your user profile. Then it’s only to navigate to the folder of “setup.exe” and run the command line. All words are typed on one line BEFORE hitting ENTER!

If your installation is made with C:\Program Files\ as the root, the setup.exe can be found here:

SQL 2008 – C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe

SQL 2008 R2 – C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe
(I found another setup.exe in the same folder as for SQL 2008, but running that file generated errors instead of databases…)

SQL 2012 – C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\Setup.exe

SQL 2014 CTP2 – C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14CTP2\Setup.exe

and my guess is that RTM of SQL 2014 will use the same path as before:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\Setup.exe 
or potentially 
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14\Setup.exe

For all Swedish speaking readers, please check out SQL Server på svenska