start SQL 2000 server _lost tempdb drive

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: start SQL 2000 server _lost tempdb drive

Thanks in advance

Replay

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.

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.

Category: sql server Time: 2010-09-05 Views: 0
Tags: sql server

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.247 (s). 12 q(s)