SQL Error 1814 / Change tempdb path / How to start SQL Server if you lost TEMPDB Data files?

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

Following are 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 execute command like

       Sqlservr.exe /f /c  

3)   Then open one more command window #2 and if this is a default instance then open SQLCMD  using the following command

SQLCMD –S localhost –E

4)   This will 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 = 'd:\tempdb\tempdb.mdf')

5> GO 

6> ALTER DATABASE tempdb MODIFY FILE

7> (NAME = templog, FILENAME = 'd:\tempdb\templog.ldf')

8> GO 

9> quit

5)   Now go back to Command window #1 and hit CTRL C.

It will ask if you want to stop the instance. Y/N.
Enter Y, Please close all Command Prompt before start the services

6)   Now start the SQL Server instance from configuration manager. You should see the new data file created and SQL Server started.