Move data or log files for TempDB in SQL Server

Since TempDB is a system database you should not try to move the database with detach/attach or backup/restore. Instead you should move the files with a simple script and after the next restart of the instance the files will be relocated to the new location.

First check where the files are currently located so that you can easily just copy and paste the name and paths if needed:
SELECT name, type_desc, physical_name 
FROM sys.master_files 
WHERE database_id = DB_ID(N'tempdb')
To do the actual change you can use the following script:
MODIFY FILE (NAME = tempdbfile, 
               FILENAME = 'C:\MyFolder\tempdb.mdf')
Change the name of the file and path to your needs. After you have executed the script you will receive a message saying that the instance needs to be started for the changes to have effect. Restart the instance and verify that the files have been relocated properly.

No comments

Post a Comment