Moved 'tempdb', now file 'tempdev2' does not exist

Question

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.

Answer 1

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

Answer 2

When moving TempDB you need to specify the filenames exactly as they are.

Your SQL instance stores this path with the file name already, and there are scripts out there that will generate the code, the only issue with them is: They do not reference the file names, often instead referencing the DB instance name as a predicate for the File Name, and it is often the case that the DB and the File Name are different. This is bad because the filename is vital for TempDB to function, and getting errors like yours is quick way to make a DBA panic.

When I was working on a migration, I altered some publicly available code to circumvent a potential issue for our TempDB. The following code might be useful for you going forward.

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder'; --Make sure to fill this in

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size / 128 AS [Size(MB)] --Number of 8KB pages / 128 = MB
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + 
    @newDriveAndFolder+right(physical_name,charindex('\',reverse(physical_name)))
    + ''');'
AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

This looks at the original string, grabs the original file name along with the current extension. This means you should be able to run the outputs and just drop the files. This is because, during my tests, some of our TempDB files were NDF and the original code wasn't catching it.

As for your current predicament, you can insert the actual file names into the statement and it should update and work.