How to start SQL Server when tempdb was relocated? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to start SQL Server when tempdb was relocated?

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-22 : 10:49:39

It's been a long time since I've tried this, but I have a SQL Server that needs to be restored (including master) to a server whose drives and corresponding folders match the source server, with the exception of tempdb. When SQL Server initially starts I believe it will fail since it cannot find tempdb. I just don't recall if it fails to startup or if it starts up reporting errors and recreates tempdb in the same location as master. Does anyone recall the steps needed to point SQL Server to the new location of tempdb?

Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 11:09:23

Run this, and then restart SQL Server. DB tempdb is recreated everytime you restart SQL Servr.


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'
	)

CODO ERGO SUM

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-22 : 11:21:30

That will only work if SQL Server is up. We're temporarily rebuilding a server off-site and the destination server has no H drive, which is where tempdb resides on the source server. The plan was to start SQL Server in single-user mode in order to restore master. Once master has been restored, it will look for an H drive to recreate tempdb. When it finds no H drive startup will fail. I don't recall how usable the server will be upon failure. I believe it will startup with errors and I will then be able to point it to the new location of tempdb, but I'm uncertain.

Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 11:47:14

The code I gave you should work OK. All that really happens is that it updates entries in master.dbo.sysaltfiles for tempdb. If it doesn't work, try updating the entries in master.dbo.sysaltfiles directly.

Of course, you don't have to take my word for it. You can test it by installing SQL Server on a temporary server, and making sure what you do works. Not a bed idea to test your whole procedure anyway.

CODO ERGO SUM

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-22 : 12:02:15

Sorry, it sounds like I'm not being clear with the situation. I know your code will work. The problem is we cannot alter the source server. Therefor when we build SQL Server on the destination server, start SQL in single user mode followed by a restore of master, SQL Server will automatically stop. When the service is restarted it will immediately look for tempdb and not find it. At this point I do not know if SQL Server is usable since it needs tempdb to start. Because of this I'm not sure I will be able to run the Alter Database command or even be able to access the system tables within master.

Thanks again for the help,

Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 13:42:41

I wasn't suggesting that you run the script on the source server, but to run it after SQL Server comes up.

You may want to read this article, if you haven't already.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-22 : 13:50:12

Michael,

How is he supposed to run that code if SQL Server will not even start?

Dave,

I think you'll need to create a temporary H drive to bring SQL Server up. Then you can run Michael's code to alter the location and then delete the H drive.

Tara Kizer
aka tduggan

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-22 : 13:54:52

Thanks Tara. That was my point. I'll install an 18GB drive as a temporary location for tempdb as you suggest. I think sqlserv -f would work, but I don't want to redo all of the custom configuration settings.

Dave

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-11-22 : 14:03:29

Dont install a new drive just use SUBST

SUBST H: C:\Temp Creates an H drive pointed to c:\Temp
SUBST H: /D Will Delete it

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 14:05:12

I just ran a test, and if you start the server with the -f parameter, you can run the alter database command to point to the location you want. Then when you restart without the -f parameter, and it will create it where you want.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources