We all know Tempdb is very important component of SQL SERVER Instance and there are some incidents where things goes crazy due to Tempdb.
I have some crazy experiences with Tempdb like giving wrong path OR forget to specify extension while shifting Tempdb database or Somehow Tempdb got crashed so the SQL Instance couldn’t able to work and will give error like “Path is not found for Tempdb database” or “Specified path is not found”.
It’s bit difficult to recover Tempdb back on track in this situation. But using command prompt we can do that.
You can follow the below steps to recover Tempdb database if SQL Instance is not able to bring up because not able to create Tempdb.
Step 01: STOP ALL Services of sql instance.
Step 02: Open cmd and execute below command:
For Default SQL Instance:
NET START MSSQLSERVER /f /T3608 -- to bypass system db recovery.
For Named Instance:
NET START MSSQL$<INSTANCE NAME>/f/T3608
Step 03: Open SQLCMD using below command in cmd prompt:
SQLCMD -S<INSTANCE NAME>
Step 04: Execute below code to change tempdb path in SQLCMD.
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev' , FILENAME = '<New Path>.mdf' )
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog' , FILENAME = '<New Path>.ldf' )
Step 05: Exit from SQLCMD USING EXIT.
Step 06: Stop SQL Service:
NET STOP MSSQLSERVER /f /T3608
NET STOP MSSQL$<INSTANCE NAME>
Step 07: Start services from config manager.
Hope this post help someone….