temp DB file does not exist after moving



  • 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?


  • You also need to check tempdb.sys.database_files. It's possible for there to be https://docs.microsoft.com/en-us/archive/blogs/sql_pfe_blog/tempdb-misconfiguration-when-sql-server-fails-to-create-a-secondary-data-file between it and sys.master_files.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2