How To Move TEMPDB In SQL Server

In this blog post we will see how can we move location of tempdb database files in sql server. Moving of database may be required for many reasons like for security purpose or space constraints or etc. As tempdb is system database it cannot be detached or attached and cannot be restored like user databases. You need to be more alert when you are planning to move tempdb database files.

Let’s see how.

Step -1 :

Check Current Location Of Tempdb Files.

Use Tempdb
Go
Select * From SysFiles
Go

Output :

You Must Note Down These Path as you need to copy MDF and LDF files to new location later on.

Step – 2:

Move Tempdb Database File locations

Alter Database Tempdb 
Modify File(Name='Tempdev', FileName='D:\Tempdb.MDF')
Go

Alter Database Tempdb 
Modify File(Name='templog', FileName='D:\Templog.LDF')
Go

Above two queries moves location of MDF and LDF files. See Following screen shot for more clarification.

Step – 3 :

Now You have changed the location of files but to activate new location you must restart the SQL Server instance Service. Before you start SQL Server Instance you need to do following 2 steps.

Verification of new file locations.

So we can check that new filenames or paths are correct if you think the path or filename is incorrect then you can run above queries to move files again with correct filename or path and check.

Step – 4

Once you have verified the new locations you need to move tempdb files ( Data and Log File) to new location. So you can copy tempdb data and log file i.e. (MDF and LDF) file from OLD Location to New Location.

Step – 5

Now after moving the files you need to restart SQL Server Services. Until You restart SQL Will use old tempdb files.