How to recover Tempdb system database step by step

Hello Friends,

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$/f/T3608 

Step 03: Open SQLCMD using below command in cmd prompt:
SQLCMD -S
GO

Step 04: Execute below code to change tempdb path in SQLCMD.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev' , FILENAME = '.mdf' )
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog' , FILENAME = '.ldf' )
GO

Step 05: Exit from SQLCMD USING EXIT.

Step 06: Stop SQL Service:

NET STOP MSSQLSERVER /f /T3608
OR
NET STOP MSSQL$

Step 07: Start services from config manager.

Then Enjoy!!!

Hope this post help someone….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s