Thursday, April 05, 2012

How to start sql server without tempdb

When i try to take image of sql server for installation to new server  i could not restart sql server service on new server due to tempdb database files missing. In that image server where tempdb database files were placed on E:\mssql\data drive and  new sql server only got C: drive  and all system dbs there on c: drive  files. This solution is tested and worked fine.

--  SQL Server Path 
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Step:1  You are going to  start sql using single user with minimal configuration on command prompt
 cmd-- > sqlservr -f -m
Now you create Folder on 'C:\MSSQL\DATA\
-- Open the second window for command prompt
c:\sqlcmd
1>
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='C:\MSSQL\DATA\Tempdb.MDF')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='C:\MSSQL\DATA\Templog.LDF')
go
>quit

7 comments:

BCSocial said...

Thanks a lot, you saved me after I deleted temdb files accidentally :).

SQLDBA said...

This is first time i heard tempdb has been deleted by accidentally. I hope wouldn't do next time.

Rob said...

Thanks....

Saved me from a world of pain!

Rob said...

thanks

saved me from a world of pain

Selçuk said...

thanks. worked for me also

Selçuk said...

thanks alot. worked for me also.

ashish malwal said...

Hi Sir i have one confusion if temp db is not there so why we need to create that one it will create automaticlly when we restrat the sql server from model db if model is corrupt then its difficult to restart. I did't understand the logic here to create tempdb .

can you please tell me little more abt it.