temp DB file does not exist after moving

Question

I am using SQL server 2019 AG.

I am facing an issue after moving temp DB files from one drive to another after locating the file paths by this script:

 Use master
 GO
 
 SELECT 
 name AS [LogicalName]
 ,physical_name AS [Location]
 ,state_desc AS [Status]
 FROM sys.master_files
 WHERE database_id = DB_ID(N'tempdb');
 GO

there were 9 files

then I use to move them :

 USE master;
 GO
 
 ALTER DATABASE tempdb 
 MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
 GO
 
 ALTER DATABASE tempdb 
 MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
 GO
 
 ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, FILENAME = ‘T:\MSSQL\DATA\tempdb_mssql_2.ndf’);
GO

I run this for all 8 files, after that I run the first script again I did not find the rest of the files I just found tempdev, and templog . and I run the moving script again :

"MODIFY FILE failed. File 'temp2' does not exist" 

so I lost all the rest of the 9 files !! 7 files are missing

• is there any way I can reattach these files?
• is my SQL server will work fine with only 2 files log and DB?

• Dan Guzman: Did you restart the instance after the move script in order to create the new tempdb files?
• Tibor Karaszi: Agree with Dan. Not doing a re-start after the move script would perfectly explain the posted behaviour.
• Tala: I did, I restarted the SQL service

Answer 1

You also need to check tempdb.sys.database_files. It's possible for there to be metadata mismatches between it and sys.master_files.