Database Simplified Headline Animator

Database Simplified

Friday 2 December 2011

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
Select * From SysFiles

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')

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

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.


  1. I enjoy, result in I found exactly what I used to be looking for. You’ve ended my 4 day long hunt! God Bless you man. Have a nice day. Jasa SEO Bye

    Regards from Young Entrepreneur Pakar Seo & Jasa SEO

    Sigit Hermawan