SQL Server instance failed to start because the partition where tempdb resides is not available, what should I do?

When my SQL 2014 virtual machine was migrated, the LUNs holding tempdb were deleted. After the operating system of the virtual machine is started, SQL Server instance does not start up. Check event Log for error.

So, I found this article.  The goal is to force SQL Server to start and allow the commands to receive sqlcmd to modify some options. Once SQL can accept the command, we can reassign the path to tempdb.

However, using the steps in the article, I still cannot force SQL Server to start.

Check the output of sqlservr.exe/f/C and find the following error:

2014-09-18 15:51:36.88 spid10s clearing tempdb database.
2014-09-18 15:51:36.89 spid10s error:5123, Severity:16, State:1.
2014-09-18 15:51:36.89 spid10s CREATE FILE encountered operating system Erro
R 3 (the system cannot find the path specified.) while attempting to open or Crea
Te the physical file ' E:\SQLDatabaseData\tempdb.mdf '.
2014-09-18 15:51:36.89 spid10s error:5123, Severity:16, State:1.
2014-09-18 15:51:36.89 spid10s CREATE FILE encountered operating system Erro
R 3 (the system cannot find the path specified.) while attempting to open or Crea
Te the physical file' F:\SQLServerDatabaseLog\templog.ldf '.
2014-09-18 15:51:36.90 spid10s error:17204, Severity:16, State:1.
2014-09-18 15:51:36.90 spid10s fcb::open failed:could not Open fileE:\SQLD
Atabasedata\tempdb.mdfFor file number 1. OS Error:3 (the system cannot find th
e path specified.).
2014-09-18 15:51:36.91 spid10s error:5120, Severity:16, state:101.
2014-09-18 15:51:36.91 spid10s Unable to open the physical file "E:\SQLDatab
Asedata\tempdb.mdf". Operating system error 3: "3 (the system cannot find the PAT
H specified.) ".
2014-09-18 15:51:36.91 spid10s error:1802, Severity:16, State:4.
2014-09-18 15:51:36.91 spid10s CREATE DATABASE failed. Some file names Liste
D could not being created. Check related errors.
2014-09-18 15:51:36.91 spid10s Could not create tempdb. Eno
Ugh disk space available. Free additional disk space by deleting other files on
The tempdb drive and then restart SQL Server. Check for additional errors in the
Event log that could indicate why the tempdb files could not be initialized.
2014-09-18 15:51:36.92 spid15s The SQL Server Network Interface library succ
essfully deregistered the Service Principal Name (SPN) [Mssqlsvc/sql2014-1. Sps.
P51359] for the SQL Server service.

It appears that the original path to the tempdb file exists even if you force SQL to start. The author then reassigned two virtual hard disks to this virtual machine. Set up the corresponding paths. SQL Server instance can finally be forced to start up.

Follow the steps in the article to solve the problem successfully.

Report

========================

To make it easier to find and prevent the loss of the original text in the future, I have listed the important steps here:

Following is the steps needed to add a new file to TEMPDB and then restart SQL Server.

1) Open Command Prompt window #1 as ADMIN and go to the BINN directory where SQL Server is installed. This is typically in

C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\binn

OR

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

2) Then the Execute command like

SQLSERVR.EXE/F/C

3) then open one to command Window #2 and if this is a default instance then open SQLCMD using the following command

Sqlcmd–s localhost–e

4) This would open a SQL command prompt there where you can type the following commands

1> Use MASTER

2> GO

3> ALTER DATABASE tempdb MODIFY FILE

4> (NAME = tempdev, FILENAME = ' C:\NEWPATH\datatempdb.mdf ')

5> GO

6> quit

5) Now go, Command window #1 and hit CTRL c.it'll ask if you want to stop the instance. y/n. Enter Y

6) Now start, the SQL Server instance from Configuration Manager. You should see the new data file created and SQL Server started.

Resources

========================

How to start SQL Server if you lost TEMPDB Data files?

http://blogs.xtivia.com/home/-/blogs/how-to-start-sql-server-if-you-lost-tempdb-data-files-

SQL Server instance cannot start because the partition where tempdb is located is not available, what should I do?