Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

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

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

    
Here is how you can create a new tempdb: support.microsoft.com/kb/288809 –  Raj More May 16 '11 at 14:35
    
@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
add comment

3 Answers

Open a command prompt, and "mount" an existing folder as your missing drive letter. For example, if TEMPDB was on G:, then try:

md d:\tempdbdisk
subst g: d:\tempdbdisk

You may need to create the directory structure that used to appear under G:\ under D:\TEMPDBDISK.

share|improve this answer
add comment

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.

share|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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.