121 lines
4.2 KiB
Transact-SQL
121 lines
4.2 KiB
Transact-SQL
/*
|
|
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; |