0

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?

Tim Schmelter
  • 163
  • 1
  • 1
  • 6

2 Answers2

1

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

Peter Schofield
  • 1,639
  • 9
  • 11
1

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.

simon
  • 714
  • 1
  • 7
  • 21