SQL Server not starting after fresh installation

Article
1/17/2024

Introduction

After the installation of Microsoft SQL Server 2014 Express we 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.

Solution description

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.

Fix the broken paths

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:

NET START MSSQLSERVER /f /T3608

Note that MSSQLSERVER is the instance name of the service that encounter the problem. If the instance name is SQLEXPRESS, you'll need to use

NET START MSSQL$SQLEXPRESS /f /T3608

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer1.png

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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer2.png

Now you want to check what is wrong with the file paths. Look up on the server for the right location, for SQL Server 2014 Express x64 the default path is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\“.

Use the following SQL commands:

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

With the following command you can 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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer3.png

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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer4.png

Reset the SA password

After fixing the paths and when you open the SQL Management Studio to check if everything is working as intended, you'll notice the users provided during the setup as administrators of the instance can’t logon. Also the ‘sa’ user is not able to logon to the instance.

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer5.png

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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer6.png

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

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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer7.png

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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer8.png

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

http://blog.brankovucinec.com/wp-content/uploads/2015/04/040715_1454_MSSQLServer9.png

Eventlog

These messages are logged

FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\model.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.x86Release\sql\mkmastr\databases\mkmastr.proj\modellog.ldf'. Diagnose and correct the operating system
 error, and retry the operation.


FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.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.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error,
 and retry the operation.

Share via