changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f