Dynamic SQL to move SQL Server TempDB Data and Log Files to different directories

The following dynamic SQL script helps you to move TempDB data and log files to different directories.

Before running the script, enter the values for the parameters @NewTempDBDataDir for the destination data file directory and @NewTempDBLogDir for TempDB new log file directory.

/*
******************************************************************
Specify the new directory name in the variable @NewTempDBDir
******************************************************************
*/
DECLARE @NewTempDBDataDir VARCHAR(1000)
SET @NewTempDBDataDir='J:\MSSQL\Data\'  
DECLARE @NewTempDBLogDir VARCHAR(1000)
SET @NewTempDBLogDir='J:\MSSQL\Logs\'  

 DECLARE @TempDBFiles TABLE
 (
    id                      INT IDENTITY(1, 1) PRIMARY KEY,
    [file_id]               INT,
    [Type] bit,
    [name]                  VARCHAR(20),
    [physical_name]         VARCHAR(1000),
    [extract_physical_name] VARCHAR(100) NULL,
    sqlscript               VARCHAR(max) NULL
 )

INSERT INTO @TempDBFiles
           ([file_id],
             [name],
             [Type],
             [physical_name])
SELECT file_id,
       NAME,
       [Type],
       physical_name
FROM   tempdb.sys.database_files

--Select * from @TempDBFiles

DECLARE @i INT
DECLARE @String NVARCHAR(max),
        @SQL   NVARCHAR(max),
        @SQLLogs NVARCHAR(MAX)

SELECT @i = Count(*)
FROM   @TempDBFiles

WHILE ( @i > 0 )
 BEGIN
     SELECT @String = physical_name
     FROM  @TempDBFiles
     WHERE file_id = @i

     UPDATE @TempDBFiles
     SET   [extract_physical_name] = 
     Reverse(Substring(Reverse(@String), 0,
    Charindex('\', 
    Reverse @String),0)))
     WHERE file_id = @i

     --select * from @TempDBFiles where file_id=@i
     SELECT @SQL = 'ALTER DATABASE [Tempdb] MODIFY FILE ( NAME='
                    + NAME + ', FILENAME= ''' + @NewTempDBDataDir
                    + extract_physical_name + ''');'
     FROM  @TempDBFiles
     WHERE file_id = @i and [Type]=0

     UPDATE @TempDBFiles
     SET   sqlscript = @SQL
     WHERE file_id = @i and [Type]=0

     SELECT @SQLLogs = 'ALTER DATABASE [Tempdb] MODIFY FILE ( NAME='
                    + NAME + ', FILENAME= ''' + @NewTempDBLogDir
                    + extract_physical_name + ''');'
     FROM  @TempDBFiles
     WHERE file_id = @i and [Type]=1

        UPDATE @TempDBFiles
     SET   sqlscript = @SQLLogs
     WHERE file_id = @i and [Type]=1

     SET @i=@i - 1
 END

SELECT name, extract_physical_name, sqlscript
FROM  @TempDBFiles  

The script generates the ALTER DATABASE statement that you can execute on the SQL Server instance for which you want to move the TempDB files (data and log ) to different drives.