After the installation of Microsoft SQL Server 2014 Express I encountered a problem that the services could not be started. It seems that the installation have set the paths to the model, MSDB and tempdev to the wrong location. After uninstall the SQL Server instance and reinstalling it, the problem persists. No settings are altered in the setup properties, also I could not reproduce this same error on another server even using the same deployment image for the Azure VM. The solution is to change the paths to those 3 databases to the right location with starting the instance in the master-only recovery mode.

First thing to do is to start the command prompt with elevated rights.

When it is started we need to start MSSQL with the following command:

040715_1454_MSSQLServer1

NET START MSSQLSERVER /f /T3608

Note that MSSQLSERVER is the instance name of the service that encounter the problem.

040715_1454_MSSQLServer2

Connect to the instance by using Windows Authentication to interactively run Transact-SQL with the following command:

SQLCMD -S .

Or with the instance name as:

SQLCMD –S .INSTANCENAME

Now you want to check with:

SELECT name, physical_name, state_desc FROM sys.master_files ORDER BY database_id;
go

What is wrong with the file paths. Look up on the server for the right location, for SQL Server 2014 Express x64 it is in my case “C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA“.

040715_1454_MSSQLServer3

With the following command I change the path of the files to the right location:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf');
ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\temp.mdf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\temp.ldf');
go

Use exit to close the SQLCMD, stop the MSSQLSERVER instance in the master-only recovery mode and start the instance normal.

040715_1454_MSSQLServer4

Then I launched the SQL Management Studio to check if everything is working as, but the users I provided during the setup as administrators of the instance can’t logon. Also the ‘sa’ user is not able to logon to the instance.

040715_1454_MSSQLServer5

Here is the solution to reset the ‘sa‘ users password.

Again open the elevated command prompt and start the instance in the master-only recovery mode.

040715_1454_MSSQLServer6

After that use the following command to reset the password of the ‘sa’ user.

ALTER LOGIN sa WITH PASSWORD = 'newpassword' UNLOCK
go
exit

040715_1454_MSSQLServer7

Restart the service of the MSSQL instance. Start the SQL Management Studio and connect with SQL Server Authentication.

040715_1454_MSSQLServer8

Now you can create the other users you need to provide access to this instance.

040715_1454_MSSQLServer9

Some of the errors in the eventlog:

FCB::Open failed: Could not open file E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projmodel.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projmodellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

FCB::Open failed: Could not open file E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projMSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projMSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.