How to Move TempDB to Another Drive & Folder

Moved a comma and added a Tab character, so the output rows line up with themselves, for that sweet sweet Alt-key block-selecting fun:

SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.[name] + ‘]’
+ CHAR(9) /* Tab */
+ ‘,FILENAME = ”Z:\MSSQL\DATA\’ + f.[name]
+ CASE WHEN f.[type] = 1 /* Log */ THEN ‘.ldf’ ELSE ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);

Maybe it’s an age/cultural thing, but, rather than rely on Google… I just have a “toolbox” folder for scripts like this, so I don’t have to look one up every time I need it.

Although the “move tempdb.sql” file in my toolbox is dated 2009 and references a URL for KB 224071 that doesn’t seem to work anymore, so I guess there’s an argument for forcing one’s self to check for something more recent! (Does one still need to stop and restart SQL Server, as I noted in comments to myself? I haven’t had to move a tempdb in a long time, I guess…)

I updated this script to allow you to change the file / folder locations at the top :

DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
, @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’

SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = ”’ + @TempLog + f.name ELSE ‘ FILENAME = ”’ + @TempData + f.name END
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);

I think that the secondary files of the type rows data must have .ndf extension. So, this will be the correct query:
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ ‘ FILENAME = ”H:\TempDB\’ + f.name
+ CASE WHEN f.type = 1 THEN ‘.ldf’ ELSE ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);

Please note that I only change de CASE statement.