May 27, 2009 at 2:44 pm
How I can I point tempdb to another drive when database instance is down. So when I start up it looks to create it in a new location. Say if I lost the drive it originally was on.
May 27, 2009 at 4:01 pm
Look up "Moving System Databases" in Books Online. Here's the simple version, assuming your SQL Server is the default instance and not a named instance.
In a command window, stop the SQL Server instance.
[font="Courier New"]NET STOP MSSQLSERVER[/font]
Restart SQL Server with only the master database.
[font="Courier New"]NET START MSSQLSERVER /f /T3608[/font]
Using SSMS or SQLCMD, execute the ALTER DATABASE commands to move the tempdb files.
[font="Courier New"]ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='new path\file')[/font]
[font="Courier New"]ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='new path\file')[/font]
(Repeat for all tempdb files if you have multiple data files)
Stop SQL Server and restart it normally.
May 27, 2009 at 4:10 pm
Sorry, the first step is not to stop the server since it is already down due to the missing tempdb drive.
Also, the normal way to move tempdb (on a running server) is to just use ALTER DATABASE and restart. It continues to use the original files, then builds new ones during the restart. You have to delete the old ones manually.
May 27, 2009 at 4:15 pm
Scott
This sounds good - I will try it tonight, Thanks!
May 28, 2009 at 4:32 pm
Worker great - thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply