SQL Server: how to move tempdb database

SQL Server doesn’t support moving TempDB Database using backup/restore or by using detach database methods. In this article I explain the steps you must follow to move TempDB database from one drive to another in SQL Server. However, for the changes to come into effect you must restart SQL Server Service.

Start SQL Server Management Studio and execute the below query:

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\yourpath\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘T:\yourpath\templog.ldf’);
GO

Remember to replace yourpath with your preferred path.

Once query is executed, SQL Server give you a message to remember you to Stop and restart the instance of SQL Server for the changes to come into effect.

After restarted, verify the changes are completed:

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’);

Now, you can delete the old tempdb files.