Configure SQL-SERVER without running service
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开发者_如何学C 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?
Take a look at the options here in MSDN
You can start up the server and amend the location of TEMPDB using an ALTER command through SQLCMD.
Alternatively you could just add another drive temporarily so that the correct path exists - TempDB is recreated on start up so just ensuring the path is available should be sufficient. You can then move the file locations if needed when the server is running.
精彩评论