Rebuild and Restore Master Database

The problem

It all starts with this error when we try to connect to SQL Server instance:

The system cannot find the file specified

At SQL Server Configuration Manager, we could see that the SQL Server service is also stopped and, when we try to start the service, we got the following error:

SQL Server configuration manager error

So we must consult the Windows Event Log to see what is the problem of the instance (Use Windows key + R and type eventvwr.exe, as shown in the next picture)

eventvwr.exe

Expand Windows Logs and then click in Application. (I also use a filter on the source column)

Event viewer - Error in master.mdf

It’s now clear that we have a problem in master database (more information about master at MSDN)!

We could also try to start the instance with minimal configuration (the -f startup parameter, more info at MSDN) but the problem remains.

The Resolution
Step 1 – Repair System Databases

So, since we couldn’t start the service we must repair the master database. Repairing master database also rebuilds all of the system databases. This means that is very important to make a restore of this databases after the repair.

To perform a system databases repair we should use the SQL Server installation media but, in version 10 and later versions, the template databases are store at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates (in the case of the default instance of SQL Server 2014) and avaliable to usage in the repair process.

Database templates location

From command prompt, go to the following path:

  • for SQL Server 2008:
    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008\
  • for SQL Server 2008R2:
    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\
  • for SQL Server 2012:
    C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\
  • for SQL Server 2014:
    C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\

And then we should run setup.exe with the following logic:

setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=«InstanceName» /SQLSYSADMINACCOUNTS=«SysSccount» [/SAPWD=«StrongPassword»] [/SQLCOLLATION=«CollationName»]

For this particular case, we don’t want to change the collation (and use the default server collation). So the command is:

setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DELL\Pedro /SAPWD=$trong3$t

After we run this command we don’t get any feedback (unless there are syntax problems or issues with parameters), so we must confirm the correct repair by open the file summary.txt at C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log (in the case of SQL Server 2014). An example of a failled repair operation:
Summary.txt with error
And an exemple of what we are looking for:

Summary.txt with passed

INFO: By analysing SQL Server log we can see that were used some startup parameters:

-s “MSSQLSERVER”
-m “SqlSetup”
-Q
-q “Latin1_General_CI_AS”
-T 4022
-T 4010
-T 3659
-T 3610
-T 8015

After repairing the system databases we should apply the latest service pack and any applicable hot fixes.

The next step is making a restore of all of system databases, using their latest backup avaliable (if we don’t change the collation in the repair process). In this example we will only be performing the restore of master database. To do that is better to use the command prompt.

Step 2 – Restore Master Database

To do a restore of the master database we must start SQL Server service in single user mode. We can do that by using a NET command and using the -m startup parameter (learn more about SQL Server Service startup options at MSDN).

NET START MSSQLSERVER /m "SQLCMD"

We get this information:

NET command

INFO: At this moment if we try to connect to SQL Server with SSMS we will get the following error message:

Error single user mode

Now connect to the instance using sqlcmd utility (more information about the usage of sqlcmd at MSDN).

HELP: If you don’t remeber the path of sqlcmd utility use: dir /s sqlcmd.exe

sqlcmd.exe location

As we can see in the image above, the path of sqlcmd utility is:

c:\Program Files\Microsoft SQL Serve\Client SQK\ODBC\110\Tools\Binn\

Type sqlcmd and we are ready to make the restore by typping:

RESTORE DATABASE master FROM DISK = 'D:\DBs\Backups\master.bak' WITH REPLACE;
GO;

The screen should be like this:

Master database restored

As you can see from the above screen, after the database as been restored, the SQL Server service is stopped.

NOTE: You should also restore MSDB and MODEL database!! But that is not in the scope of this post.

Now we can start the instance in multi-user mode typing: NET START MSSQLSERVER

Start SQL Server service in multi-user mode

The final step is recreating all the missing entries but, if we followed the best pratices for master database backup, this should not be a problem because it is a best practice to do a master database backup every time we change it.

So, at this point, we should be back online without any issues!