October 2, 2007 at 6:21 am
We use SQL Server 2005 and Windows 2003 Os
Server Configuration:
1. 2x36 GB for OS (Drive C:) Raid 0+1
We keep all system databases on this drive except for tempdb.
2. 6x146 GB SAS drives for data. (Drive D:) Raid 5
We keep all user databases and tempdb on this drive.
How do you restart SQL Server if you have moved tempdb from original location.
Scenario: We lose drive D: (where tempdb is kept)
How can we start SQL Server up so we can use our Veritas software to restore
the user databases?
Thanks,
Jeff C
jcollins
October 2, 2007 at 7:51 am
Start MSSQL via the command line (sqlserv.exe) with the -f swith (minimal configuration mode) - more info in BOL. Then run an alter to move TempDB - i.e.
use master
go
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev ,
-- New Location
FILENAME = 'D:\MSSQL\data\TEMPDB.MDF'
)
GO
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = templog ,
-- New Location
FILENAME = 'H:\MSSQL\data\TEMPLOG.LDF'
)
Tommy
Follow @sqlscribeOctober 2, 2007 at 8:05 am
TommyB has given you the solution.
Afterward you should restart sqlserver "normal".
On the other hand, I wouldn't advise tempdb to reside on Raid5 ! Best is to put is on a separate raid1 volume for write speed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 2, 2007 at 8:07 am
We are using named instances and when I run the command. I get the following.
Remember tempdb as far as the database is concerned no longer exists.
Since in our scenario we lost the D: drive where the tempdb used to be located
This is the message I get when running sqlservr.exe -f mssql.1
>>>>>>>>>>>>>>>>>>>> MS Error message >>>>>>>>>>>>>>>>>>>>>>>>
Your SQL Server installation is either corrupt or has been tampered with
(Error getting instance ID from name.). Please uninstall then re-run setup
to correct this problem.
>>>>>>>>>>>>>>>>>>>> MS Error message >>>>>>>>>>>>>>>>>>>>>>>>
Thanks,
Jeff
jcollins
October 2, 2007 at 8:16 am
To start a named instance you will need -s parameter. i.e.
sqlservr.exe -f -s
If you still get an error, start in single user mode. i.e.
sqlservr.exe -m -s
Once MSSQL is runing; connect to the instance via ISQL and execute the ALTER command from the eariler post (keep in mind TempDB is volatile, your just telling MSSQL where to create the new files for TempDB).
Tommy
Follow @sqlscribeViewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply