Tempdb data file disappearing

Question

I recently moved my Temp database files onto a separate drive (I had two data files and one log file).

However, after restarting the server, my second data file disappeared both from the file list on the gui and from sp_helpfile. I've managed to reproduce this behaviour several times: I add a new data file, see it appear both in the gui and sp_helpfile, and disappear after rebooting the server.

I've run dbcc checkdb against the database and it returns no errors. There's no information either in the SQL logs or the windows application event log.

After doing a little more research, I ran the following query:

select d.name, m.* from sys.master_files m
inner join sys.databases d 
on (m.database_id = d.database_id)
where d.name like 'tempdb'
order by 1, 2

Results: http://pastebin.com/Zu5fJ2hU

It shows four different secondary data files, none of which appear in sp_helpfile. If I try to use any of those names SQL server doesn't allow me to. None of these files physically exist on the disk. My server is SQL 2012 Standard.

Answer 1

^{(Consolidating the comments into an answer, so it's more easily consumable by searchers.)}

It appears this is related to not granting the appropriate permissions to the data container when moving the location of the physical files. Without having tried it, I believe this would also apply to user databases, not just tempdb.

I have an old, but still technically accurate, video on how to move all the system databases (not just tempdb) here, which includes showing the process of assigning permissions to the new data container. The database engine requires Full Control on This Folder, Subfolders, and Files starting at the data container root.

After the permissions are set correctly on the data container, the dangling files should be removable from the system catalogs using ALTER DATABASE ... REMOVE FILE ....