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.

Is it possible to change the location of the temp.mdf or other files without starting SQL-Server?

I wanted to reactivate an old server but one of it's USB hard disk was been already used somewhere else. Now i'm unable to start the SQL-Server service in Configuration Manager.

The event log has following entry:

Event Type: Error
Event Source:   MSSQLSERVER
Event Category: (2)
Event ID:   17207
Date:       30.08.2011
Time:       15:53:10
User:       N/A
Computer:   SQLSERV
Description: 

FCB::Open: Betriebssystemfehler 3(error not found) beim Erstellen oder Öffnen der Datei 'M:\temp.mdf'. Diagnostizieren und korrigieren Sie den Betriebssystemfehler, und wiederholen Sie den Vorgang.

Here are informations on this error.

Q: So how can i change the location of database files that reference non existing drives without having SQL-SERVER service started? I haven't seen an option in Configuration-Manager. Or is there some kind of "safe-mode" in SQL-Server?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I am assuming it is not just your temp.mdf, but all your other system databases that are missing as well...

There is a kind of safe mode, it's called "master-only recovery mode". Whether you can use it depends on whether you have a backup of your databases from your missing hard drive.

If you have a backup of your database instance "data" folder, then this article has some useful information in the "Failure Recovery Procedure" section. I would also look at the section "Moving the master and Resource Databases". Are you able to follow the steps in this without sql server started?

If not, you could play with the startup options. The sql server process has startup options to specify an alternate master database location. You can also change the the startup options in the registry here: HKLM\Software\microsoft\microsoft sql server\MSSQL.1\mssqlserver\parameters (assuming this is the first or only database instance you have installed).

If you don't have a backup of the data folder, then you should look at rebuilding the master database, which involves running the sql server setup again using the log of your original installation.

EDIT: Is it just the tempdb on the missing hard disk? If so you may be able to enter master only recovery mode and move the temp database as described in the first link under "Failure Recovery Procedure" steps 1-2. Then follow the example of how to move the tempdb at the bottom.

share|improve this answer

I don't believe you can, except a special start-up sequence called master-only I believe. It's where the user databases aren't mounted, and I believe TempDB isn't created.

Is it possible to temporarily present a M: drive, with suitable permissions.... so you get SQL Server running, and then change the TempDB location to something that will work permanently?

System databases - Master only. http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.100).aspx

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.