Home / server / Questions / 178343
In Process
Manjot
Asked: 2010-09-06 15:02:51 +0800 CST

start SQL 2000 server _lost tempdb drive

  • 772

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: alt text

Thanks in advance

sql-server

2 Answers

  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.

    • 2

  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.

    • -1