up vote 0 down vote favorite
share [g+] share [fb]

We had a SQL 2K server with tempdb set to a separate disk than SQL and OS. That disk had a problem and I need to start SQL with everything pointing to the C drive with SQL and the OS. At this point the service won't start because it's looking to create the tempdb on the other disk, no longer available.

Mounting a new disk and assigning the same drive letter as the old one is not an option at this point.

link|improve this question
We should move the to ServerFault - the admins there will give you better answers. – Raj More May 16 '11 at 14:34
Here is how you can create a new tempdb: support.microsoft.com/kb/288809 – Raj More May 16 '11 at 14:35
How can I move the question to ServerFault? – Joel May 16 '11 at 14:37
@Raj More, the KB you posted is for suspect tempdbs. My problem is a non-existent tempdb. The server won't even start if it can't create the tempdb. The problem is that the server is set up to create the tempdb on a non-existent disk. – Joel May 16 '11 at 14:38
@Joel - It will get migrated when it receives 5 votes. It currently has 3. Click "Close -> Off Topic -> Server Fault" (assuming you see these links on your own question.) – Martin Smith May 16 '11 at 14:39
show 1 more comment
feedback

migrated from stackoverflow.com May 18 '11 at 9:23

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote

You will have to put a new disk and assign the same drive letter just to start the service and transfer the files (tempdb and templog).

In this case, after the connection to the DB use this script:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

Don't forget to restart the service after running the above.

Look here for details.

link|improve this answer
I don't want to change the location at this point. The SQL server is not starting. I just need to know how to start the service if the tempdb create location is not available anymore. – Joel May 16 '11 at 14:40
@Parkyprg - is adding a new disk with the same drive letter the only option? At this point, I'm looking at other alternatives. – Joel May 16 '11 at 14:43
I don't know if this is possible without starting the service. – Parkyprg May 16 '11 at 14:43
@Joel: Check Gail Erickson's last post on page (almoust down at the end). social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/… - maybe you can use the second option. – Parkyprg May 16 '11 at 14:51
Ended up with the 'new disk' solution. – Joel May 16 '11 at 18:58
feedback
up vote 0 down vote

I know that it's too late, but there's a document in Books Online titled "Moving System Databases" that may be of use to you in the future.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard