Lost drive with tempdb, SQL Server won't start

[RRinTetons]

Server 2008
SQL Server 2008 R2
64-bit

I lost the drives with files for an application and SQL Server's tempdb. I'm trying to bring the server back up with alternate storage, but SQL Server won't start since it can't find the tempdb.

Since the application's data files are toast as I need to restore anyway it doesn't seem like there's an issue for the tempdb; I just need one.

How can I tell SQL Server to create one someplace and start using it?

-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

You need to start tempdb in single user mode running in a minimal configuration mode. To do this open a command prompt and navigate to the directory with the SQL Server binaries in it (c:\Program Files\Microsoft SQL SErver\MSSQL105.MSSQLSERVER\MSSQL\Binn\ or something close by default). Then run "sqlservr.exe -c -f -m". This will run SQL Server within the command window. You can then connect from another window using sqlcmd and use the ALTER DATABASE command to move the tempdb database to another location. Then press <CTRL>+C in the window running SQL Server to stop it, then start it normally.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[RRinTetons]

I wish the problem had occurred on a week day when I could've gotten this response from you! Unfortunately, I had to move ahead. So I took the Big Hammer approach and simply uninstalled SQL Server and reinstalled from scratch. That caused modest havoc, but got transaction processing back online after one and a half all nighters.

I have archived your response. I hope to never need the information, but if I do, that's can make life a lot better.

Out of curiosity, what part of your background enables you to know that? Is that something you got from some sort of training, or from years in the trenches? I knew there had to be a way to do it, I knew it probably involved the command line, and I knew it was probably a flag to sqlservr.exe, but I was unable to find any reference to which flags or how to use them.

Thank you, very much.

-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

It is always good information to keep around. You never know when you'll need it.

I've been doing this for a while and I've had to fix some pretty big problems before. This is just one of the problems that I've run across in the past. You are probable the third person that I've had to give these instructions to in the last couple of months.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[RRinTetons]

I figured it wasn't straight out of a specific Microsoft training course. Drat.

I'm going to post another question about recovering data from tran logs when I don't have the full backup to integrate them to. I'll bet you'll have input there, too .

-
Richard Ray
Jackson Hole Mountain Resort

 

[mrdenny]

Yep, sure do. See other thread.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site