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.