start SQL 2000 server _lost tempdb drive

Question

If the temp db drive is unavailable (others system database files are available), how can I trick SQL server 2000 to create new temp db on C: and start up?

When trying to start up using sqlservr -c -f -T3608 -T4022 from command prompt, it just displays the following and thats it:

Thanks in advance

Answer 1

You can run the following from a command prompt to run SQL Server in the command prompt. First, change directories to the SQL server executables folder, (possibly C:\Program Files\Microsoft SQL Server\MSSQL\Binn).

sqlservr -f -s "SQL_INSTANCE"

Then, if you want to permanently change the tempdb location, your can run the following SQL:

USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\<folders>\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\<folder>\templog.ldf')
go

Stop the SQL Server (control-c) in the command prompt and start the SQL Server service as normal.

Answer 2

Use this code: you will need to know the logical names of the tempdb database files. The logical name for each file is in the NAME column

USE tempdb
GO
EXEC sp_helpfile

Change the location of each file using ALTER DATABASE.

USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb, FILENAME = 'C:\tempdb\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdblog,FILENAME = 'C:\tempdb\templog.ldf')
GO

Stop and restart SQL Server.