title: Move data or log files for TempDB in SQL Server
date: 2014-09-30 14:14:00 +0200 CEST
draft: false
author: John Roos
----
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:
ALTER DATABASE tempdb
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.
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:
ALTER DATABASE tempdb
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.
- Written by John Roos on September 30, 2014