MsSQL: Move tempdb

When you need to move a database in MsSQL -in this case the tempdb- you can use two scripts:

use tempdb
go
sp_helpfile
go

 

When running this script, it will show the location of the tempdb database

This script is usefull to determine the file location of the databases. After relocating the database, these files can be removed.

With the following script, you can move the tempdb database to another location:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\DATA\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'F:\MSSQL\DATA\templog.ldf')
go

After this script is run, the SQL services need to be restarted and the old files can be removed.

Leave a Reply

Your email address will not be published. Required fields are marked *