SQL with Manoj

 
Today we will see how to move your SQL Server tempDB database from its default location to an another drive. Reason could be anything like Disk maintenance, Failure recovery, etc. This can be done by using the normal ALTER DATABASE statement, we will see how.
 

–> Script: Let’s first check the location of master DB:

USE tempdb
GO

SELECT * FROM sys.database_files

–> Now execute the ALTER DATABASE statement with MODIFY FILE option:

USE master;  
GO

ALTER DATABASE tempdb   
MODIFY FILE (
	NAME = tempdev, 
	FILENAME = 'E:\SystemDatabases\tempDB\tempdb.mdf'
);
GO

ALTER DATABASE tempdb   
MODIFY FILE (
	NAME = templog, 
	FILENAME = 'E:\SystemDatabases\tempDB\templog.ldf'
);
GO

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.
 

–> Now Restart the SQL Server services from SSMS Object Explorer or by going to RUN –> services.msc.

As soon as the SQL Server services are started, the SQL engine re-creates the tempDB database files. So there is no need to physically move them to the new location.
 

–> To confirm the new location, just execute following query and check the path: