.::: Move TempDB to other folder (move SQL Server MDF and LDF Files location):::.

 

 
correlation https://teguhth.blogspot.com/2023/06/move-sql-server-mdf-and-ldf-files.html

1. check 

sp_helpdb tempdb

USE tempdb;
GO
SELECT 
    name AS [Logical Name],
    physical_name AS [File Path],
    type_desc AS [File Type],
    size * 8 / 1024 AS [Size (MB)],
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

 

2. original plan move to C:\tempfiledb

 
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\templog.ldf
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf
 

plan move to 

C:\tempfiledb\tempdev.mdf
C:\tempfiledb\templog.ldf
C:\tempfiledb\temp2.mdf

3. move / change location tempdb 

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempfiledb\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'C:\tempfiledb\templog.ldf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = temp2, FILENAME = 'C:\tempfiledb\tempdb_mssql_2.ndf');
GO
 

4. if using query 

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
    + ' FILENAME = ''C:\tempfiledb\' + 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');
 

and get 

ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = 'C:\tempfiledb\tempdev.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = 'C:\tempfiledb\templog.ldf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = 'C:\tempfiledb\temp2.mdf');

5. Stop SQL Server service via cmd:

net stop MSSQLSERVER
 

6. move physical tempdb 

move "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb.mdf" C:\tempfiledb

move "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\templog.ldf" C:\tempfiledb
move "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf" C:\tempfiledb
  

 

7. Start lagi SQL Server service:

net start MSSQLSERVER
 

8. Verification

sp_helpdb tempdb

USE tempdb;
GO
SELECT 
    name AS [Logical Name],
    physical_name AS [File Path],
    type_desc AS [File Type],
    size * 8 / 1024 AS [Size (MB)],
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');