Sql-server – Moved ‘tempdb’, now file ‘tempdev2’ does not exist

sql serversql-server-2008sql-server-2008-r2tempdb

I moved the tempdb first locating the files:

use master 
select name, physical_name
from sys.master_files
where database_id = DB_ID('tempdb');
GO

then I moved each file:

use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\')
go
alter database tempdb
modify file (name = templog, filename = 'S:\')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\')
go

The ndf tempdev2 did not move, though the path was set. My SQL Server will not start unless I use the "/f" switch. When I retry the move or set the path to original location I get the following message:

Msg 5041, Level 16, State 1, Line 1 MODIFY FILE failed. File
'tempdev2' does not exist.

Any ideas? tempdev and templog are fine, just the tempdev1 ndf.
Is it possible to set the path for tempdev2 via the registry?

The mistake I made was probably not shrinking the ndf before the move. Now I can not shrink it because the path is not correct.

Additional information

I know get the following error in event viewer:

CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file 'S:\tempdb'.

I checked permissions on the partition and folder, and everyone has full access and this permission error happens.

Best Answer

You are not providing a unique physical filename for each file. You should alter tempdb like this:

use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\tempdev.mdf')
go
alter database tempdb
modify file (name = templog, filename = 'S:\templog.ldf')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\tempdev2.mdf')
go

Microsoft Docs for ALTER DATABASE file and filegroup options state the following:

To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
Related Question