It all starts with this error when we try to connect to SQL Server instance:
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:
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)
Expand Windows Logs and then click in Application. (I also use a filter on the source column)
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.
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.
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:
And an exemple of what we are looking for:
INFO: By analysing SQL Server log we can see that were used some startup parameters:
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:
INFO: At this moment if we try to connect to SQL Server with SSMS we will get the following error message:
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
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:
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
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!