/*
    Simple TempDB Files Move Script  
    Purpose: Move TempDB files to new volume WITHOUT changing configuration
    Author: Laurent Perroud
    Date: 18.02.2026
    
    IMPORTANT: This script only moves files, keeps current sizes and growth settings.
    SQL Server service restart is required after executing the commands.
*/

SET NOCOUNT ON;

-- Parameters (to be replaced by PowerShell script)
DECLARE @NewTempDBPath NVARCHAR(500) = 'M:\sqlTemp'
DECLARE @TempDBLogPath NVARCHAR(500) = 'M:\sqlTemp'  -- Optional: separate path for log files

-- Declare variables
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @LogicalName NVARCHAR(128)
DECLARE @NewPhysicalPath NVARCHAR(500)
DECLARE @LogPath NVARCHAR(500)

-- Determine log path (use separate log path if provided, otherwise use same as data)
SET @LogPath = CASE 
    WHEN @TempDBLogPath <> @NewTempDBPath AND ISNULL(@TempDBLogPath, '') <> '' 
    THEN @TempDBLogPath 
    ELSE @NewTempDBPath 
END

PRINT '-- ========================================'
PRINT '-- Simple TempDB Move Script Generated: ' + CONVERT(NVARCHAR, GETDATE(), 120)
PRINT '-- Target Data Path: ' + @NewTempDBPath 
PRINT '-- Target Log Path: ' + @LogPath
PRINT '-- Mode: Move files only (preserve current configuration)'
PRINT '-- ========================================'
PRINT ''

-- Get current TempDB files for reference
PRINT '-- Current TempDB File Configuration:'
SELECT 
    name AS LogicalName,
    physical_name AS CurrentPhysicalPath,
    size * 8 / 1024 AS CurrentSizeMB,
    type_desc AS FileType
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY file_id;

PRINT ''
PRINT '-- Generated ALTER DATABASE Commands:'
PRINT '-- Execute these commands then restart SQL Server service'
PRINT ''

-- Move existing TempDB files (data and log) preserving current configuration
DECLARE file_cursor CURSOR FOR
SELECT name, physical_name, type
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type, file_id;  -- Data files first, then log

OPEN file_cursor;
DECLARE @PhysicalName NVARCHAR(500);
DECLARE @FileName NVARCHAR(255);
DECLARE @FileType INT;
FETCH NEXT FROM file_cursor INTO @LogicalName, @PhysicalName, @FileType;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Extract the filename from the current physical path
    SET @FileName = RIGHT(@PhysicalName, CHARINDEX('\', REVERSE(@PhysicalName)) - 1)
    
    -- Build the new physical path using the appropriate target directory
    IF @FileType = 0  -- Data file
        SET @NewPhysicalPath = @NewTempDBPath + '\' + @FileName
    ELSE  -- Log file  
        SET @NewPhysicalPath = @LogPath + '\' + @FileName
    
    -- Generate ALTER DATABASE command (only changing the path)
    SET @SQL = 'ALTER DATABASE tempdb MODIFY FILE (NAME = ''' + @LogicalName + ''', FILENAME = ''' + @NewPhysicalPath + ''');'
    
    PRINT @SQL
    
    FETCH NEXT FROM file_cursor INTO @LogicalName, @PhysicalName, @FileType;
END;

CLOSE file_cursor;
DEALLOCATE file_cursor;

PRINT ''
PRINT '-- ========================================'
PRINT '-- SIMPLE MIGRATION PROCEDURE:'
PRINT '-- ========================================'
PRINT '-- 1. Create target directories:'
PRINT '--    Data: ' + @NewTempDBPath
PRINT '--    Log:  ' + @LogPath  
PRINT '-- 2. Execute the above ALTER DATABASE commands'
PRINT '-- 3. Stop SQL Server service'
PRINT '-- 4. Start SQL Server service (TempDB files will be recreated in new location)'
PRINT '-- 5. Verify new file locations'
PRINT '-- 6. Delete old TempDB files after verification'
PRINT ''

PRINT '-- PowerShell commands to create target directories:'
PRINT '-- New-Item -Path "' + @NewTempDBPath + '" -ItemType Directory -Force'
IF @LogPath <> @NewTempDBPath
    PRINT '-- New-Item -Path "' + @LogPath + '" -ItemType Directory -Force'
PRINT ''

-- Generate validation script
PRINT '-- Post-restart validation script:'
PRINT 'SELECT name AS LogicalName, physical_name AS PhysicalPath, type_desc AS FileType, size*8/1024 AS SizeMB'
PRINT 'FROM sys.master_files WHERE database_id = DB_ID(''tempdb'') ORDER BY type, file_id;'
PRINT ''

-- Show current TempDB file locations for reference
PRINT '-- Current TempDB file locations (for cleanup reference):'
SELECT 
    '-- Old location to cleanup: ' + physical_name AS CurrentLocations
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type, file_id;