Created
September 4, 2025 20:42
-
-
Save nanoDBA/0a43cc273232a06746e270b601f8e72c to your computer and use it in GitHub Desktop.
Safely migrate SQL Server TempDb files to a new location, following best practices for file count, permissions, and logging. Downtime required; use with caution in production.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .SYNOPSIS | |
| Moves SQL Server TempDb files to a new location with best practices. | |
| .DESCRIPTION | |
| This script safely relocates SQL Server TempDb files to a specified path. | |
| It handles: | |
| - File count optimization based on CPU cores (Best Practices) | |
| - Preserving or standardizing filenames | |
| - Robust directory creation and permission setup (with `xp_cmdshell` fallback) | |
| - Comprehensive logging and parameter preservation | |
| The script will restart SQL Server during the migration process, causing temporary downtime. | |
| The migration process: | |
| 1. Validates target directory and checks for existing files. | |
| 2. Creates/verifies target directory permissions using `New-DbaDirectory` or `xp_cmdshell` fallback. | |
| 3. Captures original SQL Server startup parameters for preservation. | |
| 4. Stops SQL Server Agent and SQL Server services. | |
| 5. Starts SQL Server normally to execute `ALTER DATABASE` commands. | |
| 6. Stops SQL Server again for physical file cleanup. | |
| 7. Removes old TempDb files from the previous location. | |
| 8. Performs a final normal restart of SQL Server (all databases come online). | |
| 9. Verifies TempDb files are in the new location and startup parameters are preserved. | |
| .PARAMETER SqlInstance | |
| The SQL Server instance to target. Default is 'localhost'. | |
| For named instances use format: 'SERVER\INSTANCE' | |
| .PARAMETER TargetPath | |
| The destination path for TempDb files. Must end with backslash (\). | |
| Default is 'A:\SqlTempDb\' | |
| .PARAMETER BestPractices | |
| When enabled, adjusts TempDb file count to match logical CPU cores (up to 8). | |
| Also sets appropriate file sizes, growth settings, and max sizes. | |
| Default is $true. | |
| .PARAMETER PreserveNames | |
| When enabled, keeps the original TempDb filenames. | |
| When disabled, uses standardized naming (tempdb.mdf, templog.ldf, temp2.ndf, etc.). | |
| Default is $true for safety. | |
| .PARAMETER LogToFile | |
| When enabled, logs all operations to a file. | |
| Useful for troubleshooting and audit purposes. | |
| .PARAMETER LogPath | |
| Directory path for log files when -LogToFile is enabled. | |
| Default is current user's Documents folder. | |
| Log filename format: TempDb-Migration-YYYYMMDD.log | |
| .PARAMETER Force | |
| Skips the safety confirmation prompt. Use with caution in production! | |
| Also proceeds even if target directory contains existing files. | |
| .PARAMETER WhatIf | |
| Shows what changes would be made without actually performing them. | |
| Useful for previewing the migration plan. | |
| .PARAMETER Verbose | |
| Enables detailed verbose logging for all operations including: | |
| - dbatools command execution details | |
| - Service start/stop operations | |
| - SQL query execution results | |
| - Timing information for key phases | |
| - Connection test details | |
| .PARAMETER SetDelayedStart | |
| Sets SQL Server and SQL Agent services to delayed-auto start. | |
| Useful for ephemeral storage scenarios (Azure VMs, cloud instances). | |
| Default is to leave service startup type unchanged. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" | |
| Basic usage - moves TempDb to E:\TempDb\ with default settings. | |
| Will prompt for confirmation before proceeding. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "D:\SQLData\TempDb\" -Force | |
| Moves TempDb without confirmation prompts. Use with caution! | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -WhatIf | |
| Preview what changes would be made without actually performing them. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -BestPractices:$false | |
| Moves TempDb but keeps the current file count (doesn't optimize for CPU cores). | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -PreserveNames:$false | |
| Moves TempDb and standardizes filenames to tempdb.mdf, templog.ldf, temp2.ndf, etc. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -Force -LogToFile | |
| Production migration with detailed logging and no confirmation prompts. | |
| Log will be written to user's Documents folder. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -Force -LogToFile -LogPath "C:\Logs" | |
| Production migration with logging to a custom directory. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -Verbose | |
| Migration with detailed verbose output showing all dbatools operations and timing. | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -TargetPath "E:\TempDb\" -SetDelayedStart | |
| Moves TempDb and sets SQL services to delayed-auto start (useful for ephemeral storage). | |
| .EXAMPLE | |
| .\Move-TempDb.ps1 -SqlInstance "SQLPROD01\INSTANCE1" -TargetPath "T:\TempDb\" | |
| Targets a specific named SQL instance on server SQLPROD01. | |
| .NOTES | |
| Author: nanoDBA / AI | |
| Requires: Administrator privileges, dbatools module | |
| Impact: SQL Server will be restarted (causes downtime) | |
| Expected Downtime: 5-15 minutes (due to full SQL Server restarts) | |
| Prerequisites: | |
| - Install dbatools: Install-Module dbatools -Scope AllUsers -Force | |
| - Run as Administrator | |
| Best Practices: | |
| - Test in non-production first | |
| - Schedule during maintenance window | |
| - Ensure target drive has sufficient space | |
| - **Verify SQL Service account has permissions to target path** (script attempts to set this) | |
| - Use fast storage (SSD/NVMe) for TempDb when possible | |
| .LINK | |
| https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database | |
| .LINK | |
| https://dbatools.io/ | |
| #> | |
| param( | |
| [string]$SqlInstance = 'localhost', | |
| [string]$TargetPath = 'A:\SqlTempDb\', # you likely want to change this | |
| [switch]$BestPractices = $true, # Adjust file count to match CPU cores (default: true) | |
| [switch]$PreserveNames = $true, # Keep original filenames (default: true for safety) | |
| [switch]$LogToFile, | |
| [string]$LogPath = "$([Environment]::GetFolderPath('MyDocuments'))", | |
| # Default to user's Documents folder | |
| [switch]$Force, # Skip confirmation prompt (I understand the risks) | |
| [switch]$WhatIf, # Show what would happen without making changes | |
| [switch]$Verbose, # Enable verbose logging for all operations | |
| [switch]$SetDelayedStart # Set SQL services to delayed-auto start (for ephemeral storage) | |
| ) | |
| # Check PowerShell version compatibility | |
| if ($PSVersionTable.PSVersion.Major -lt 3) { | |
| Write-Host "ERROR: This script requires PowerShell 3.0 or higher" -ForegroundColor Red | |
| Write-Host "Current version: $($PSVersionTable.PSVersion)" -ForegroundColor Yellow | |
| exit 1 | |
| } | |
| # Check for dbatools module and import it | |
| $dbatoolsAvailable = Get-Module -ListAvailable -Name dbatools | |
| if (-not $dbatoolsAvailable) { | |
| Write-Host "ERROR: dbatools module is not installed" -ForegroundColor Red | |
| Write-Host "Please install it using: Install-Module dbatools -Scope CurrentUser" -ForegroundColor Yellow | |
| Write-Host "Note: dbatools supports PowerShell 3+ on Windows, 7.4+ on Linux/macOS" -ForegroundColor Gray | |
| exit 1 | |
| } | |
| # Import the module if not already imported | |
| if (-not (Get-Module dbatools)) { | |
| Import-Module dbatools -ErrorAction Stop | |
| } | |
| # Setup | |
| Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true | |
| $ErrorActionPreference = 'Stop' | |
| $targetPath = $TargetPath.TrimEnd('\') + '\' | |
| # Set verbose preference if requested | |
| if ($Verbose) { | |
| $VerbosePreference = 'Continue' | |
| } | |
| # Logging function with timestamped output | |
| function Write-LogMessage { | |
| param( | |
| [Parameter(Mandatory=$true)] | |
| [string]$Message, | |
| [Parameter(Mandatory=$false)] | |
| [string]$Color = 'Gray', | |
| [Parameter(Mandatory=$false)] | |
| [switch]$IsVerbose | |
| ) | |
| $timestamp = Get-Date -Format 'HH:mm:ss' | |
| # Only show verbose messages if verbose is enabled | |
| if ($IsVerbose -and -not $Verbose) { | |
| return | |
| } | |
| $prefix = if ($IsVerbose) { "VERBOSE" } else { "" } | |
| $displayMessage = if ($prefix) { "[$timestamp] $prefix $Message" } else { "[$timestamp] $Message" } | |
| Write-Host $displayMessage -ForegroundColor $Color | |
| if ($LogToFile) { | |
| $logFile = Join-Path $LogPath "TempDb-Migration-$(Get-Date -Format 'yyyyMMdd').log" | |
| # Ensure log directory exists | |
| if (-not (Test-Path $LogPath)) { | |
| New-Item $LogPath -ItemType Directory -Force | Out-Null | |
| } | |
| Add-Content $logFile "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - $Message" -ErrorAction SilentlyContinue | |
| } | |
| } | |
| # Show disclaimer and get confirmation | |
| if (-not $Force -and -not $WhatIf) { | |
| Write-Host "`n" -NoNewline | |
| Write-Host "+--------------------------------------------------------------+" -ForegroundColor Red | |
| Write-Host "¦ CRITICAL WARNING ¦" -ForegroundColor Red | |
| Write-Host "¦--------------------------------------------------------------¦" -ForegroundColor Red | |
| Write-Host "¦ This script will STOP SQL Server and move TempDb files. ¦" -ForegroundColor Yellow | |
| Write-Host "¦ This WILL cause a production outage! ¦" -ForegroundColor Yellow | |
| Write-Host "¦ ¦" -ForegroundColor Yellow | |
| Write-Host "¦ DO NOT RUN IN PRODUCTION without: ¦" -ForegroundColor Yellow | |
| Write-Host "¦ • Approved maintenance window ¦" -ForegroundColor Yellow | |
| Write-Host "¦ • Verified backups ¦" -ForegroundColor Yellow | |
| Write-Host "¦ • Rollback plan ¦" -ForegroundColor Yellow | |
| Write-Host "¦ ¦" -ForegroundColor Yellow | |
| Write-Host "+--------------------------------------------------------------+" -ForegroundColor Red | |
| Write-Host " Target: $SqlInstance" -ForegroundColor Cyan | |
| Write-Host " Moving to: $targetPath" -ForegroundColor Cyan | |
| Write-Host "`n" | |
| $response = Read-Host "Type 'YES I UNDERSTAND' to proceed, or press Ctrl+C to cancel" | |
| if ($response -ne 'YES I UNDERSTAND') { | |
| Write-Host "Operation cancelled." -ForegroundColor Yellow | |
| exit 0 | |
| } | |
| } | |
| try { | |
| $scriptStartTime = Get-Date | |
| if ($WhatIf) { | |
| Write-LogMessage "TempDb Migration Preview (WhatIf Mode)" "Cyan" | |
| Write-LogMessage "No changes will be made - this is a preview only" "Yellow" | |
| Write-LogMessage "Actual migration would expect downtime: 5-15 minutes" "Gray" | |
| } else { | |
| Write-LogMessage "TempDb Migration Starting" "Cyan" | |
| Write-LogMessage "Expected downtime: 5-15 minutes (due to full SQL Server restarts)" "Yellow" | |
| Write-LogMessage "Migration started at: $($scriptStartTime.ToString('yyyy-MM-dd HH:mm:ss'))" -IsVerbose | |
| } | |
| # Log verbose mode status | |
| if ($Verbose) { | |
| Write-LogMessage "Verbose logging enabled" "Cyan" | |
| } | |
| # Check for multiple SQL instances (only if using default localhost) | |
| if ($SqlInstance -eq 'localhost' -or $SqlInstance -eq '.') { | |
| $instances = Get-Service -Name 'MSSQL$*','MSSQLSERVER' -ErrorAction SilentlyContinue | | |
| Where-Object { $_.Status -eq 'Running' } | |
| if ($instances.Count -gt 1) { | |
| Write-LogMessage "ERROR: Multiple SQL Server instances detected:" "Red" | |
| $instances | ForEach-Object { | |
| Write-LogMessage " - $($_.DisplayName) [$($_.Name)]" "Yellow" | |
| } | |
| Write-LogMessage "`nThis script cannot determine which instance to migrate." "Red" | |
| Write-LogMessage "Please either:" "Yellow" | |
| Write-LogMessage " 1. Specify the instance: -SqlInstance 'ServerName\InstanceName'" "Cyan" | |
| Write-LogMessage " 2. Stop other instances before running" "Cyan" | |
| Write-LogMessage "`nAborting to prevent accidental changes to wrong instance." "Red" | |
| exit 1 | |
| } | |
| } | |
| # Determine the service name for the target instance | |
| $serviceName = 'MSSQLSERVER' | |
| $instanceName = $null | |
| if ($SqlInstance -match '\\(.+)$') { | |
| $instanceName = $matches[1] | |
| $serviceName = "MSSQL`$$instanceName" | |
| Write-LogMessage "Target instance: $instanceName (Service: $serviceName)" "Gray" | |
| } | |
| # Get current config | |
| Write-LogMessage "Querying current TempDb configuration" -IsVerbose | |
| $query = @" | |
| SELECT file_id, name, type, physical_name, size*8/1024 size_mb | |
| FROM sys.master_files WHERE database_id = DB_ID('tempdb') | |
| ORDER BY type, file_id | |
| "@ | |
| $files = Invoke-DbaQuery -SqlInstance $SqlInstance -Query $query -Verbose:$Verbose | |
| Write-LogMessage "Found $($files.Count) TempDb files" -IsVerbose | |
| $dataFiles = $files | Where-Object { $_.type -eq 0 } | |
| $logFiles = $files | Where-Object { $_.type -eq 1 } | |
| # Determine target file count (following dbatools best practices) | |
| # Best practice: File count should match logical cores up to 8 | |
| Write-LogMessage "Running TempDb best practices analysis" -IsVerbose | |
| $cores = (Test-DbaTempDbConfig -SqlInstance $SqlInstance -Verbose:$Verbose | Where-Object { $_.Rule -eq 'File Count' }).Recommended | |
| Write-LogMessage "Recommended data file count based on CPU cores: $cores" -IsVerbose | |
| $targetCount = if ($BestPractices -and $cores) { | |
| $coreCount = [int]$cores | |
| # Cap at 8 per Microsoft recommendations | |
| if ($coreCount -gt 8) { 8 } else { $coreCount } | |
| } else { | |
| $dataFiles.Count | |
| } | |
| # Calculate equal size per file (best practice: equal-sized data files) | |
| $totalSizeMB = ($dataFiles | Measure-Object -Property size_mb -Sum).Sum | |
| if ($totalSizeMB -lt 1024) { $totalSizeMB = 1024 } # Minimum 1GB total | |
| $sizePerFile = [math]::Ceiling($totalSizeMB / $targetCount) | |
| Write-LogMessage "Current: $($dataFiles.Count) files, Target: $targetCount files at ${sizePerFile}MB each" | |
| # Check if migration needed | |
| $needsMove = ($files | Where-Object { $_.physical_name -notlike "$targetPath*" }).Count -gt 0 | |
| $needsResize = $BestPractices -and ($dataFiles.Count -ne $targetCount) | |
| if (-not $needsMove -and -not $needsResize) { | |
| Write-LogMessage "No changes needed" "Green" | |
| return | |
| } | |
| # Validate and create target directory | |
| if ($WhatIf) { | |
| Write-LogMessage "[WhatIf] Would validate target directory: $targetPath" "Cyan" | |
| if ($targetPath -like 'C:\*') { | |
| Write-LogMessage "[WhatIf] Would show warning: Target path is on C: drive" "Cyan" | |
| } | |
| Write-LogMessage "[WhatIf] Would create/verify target directory permissions" "Cyan" | |
| } else { | |
| # Check if target drive exists first | |
| $targetDrive = $targetPath.Substring(0,2) | |
| if (-not (Test-Path $targetDrive)) { | |
| Write-LogMessage "ERROR: Target drive $targetDrive does not exist or is not accessible" "Red" | |
| $availableDrives = (Get-PSDrive -PSProvider FileSystem | Where-Object { $_.Used -ne $null }).Name | |
| Write-LogMessage "Available drives: $($availableDrives -join ', ')" "Yellow" | |
| Write-LogMessage "Please specify a valid target path with -TargetPath parameter" "Cyan" | |
| throw "Target drive $targetDrive not accessible" | |
| } | |
| Write-LogMessage "Setting up target directory" | |
| # Ensure path ends with backslash (from DANGER script logic) | |
| if (-not $targetPath.EndsWith('\')) { | |
| throw "Target path must end with backslash (\)" | |
| } | |
| # Best practice warning: TempDb on C: drive | |
| if ($targetPath -like 'C:\*') { | |
| Write-LogMessage "WARNING: Target path is on C: drive" "Yellow" | |
| Write-LogMessage "Best practice: TempDb should be on a dedicated, fast drive" "Yellow" | |
| if (-not $Force) { | |
| Write-LogMessage "Use -Force to skip this warning" "Gray" | |
| } | |
| } | |
| # Check if directory exists and has files | |
| $dirAlreadyExisted = (Test-Path $targetPath) | |
| if ($dirAlreadyExisted) { | |
| $existingFiles = Get-ChildItem "$targetPath*.?df" -ErrorAction SilentlyContinue | |
| if ($existingFiles) { | |
| Write-LogMessage "WARNING: Target directory already contains database files:" "Yellow" | |
| $existingFiles | ForEach-Object { | |
| $sizeMB = [math]::Round($_.Length/1MB, 1) | |
| Write-LogMessage " $($_.Name) ($sizeMB MB)" "Yellow" | |
| } | |
| if (-not $Force) { | |
| Write-LogMessage "These files may be from a previous incomplete migration" "Yellow" | |
| Write-LogMessage "Use -Force to proceed anyway" "Red" | |
| throw "Target directory contains existing database files" | |
| } | |
| } else { | |
| Write-LogMessage "Target directory exists: $targetPath (empty)" "Gray" | |
| } | |
| } | |
| # Create directory with permissions using dbatools | |
| try { | |
| Write-LogMessage "Attempting to create/verify target directory permissions with dbatools" "Gray" | |
| Write-LogMessage "Calling New-DbaDirectory for path: $targetPath" -IsVerbose | |
| $dirResult = New-DbaDirectory -SqlInstance $SqlInstance -Path $targetPath -EnableException -Verbose:$Verbose | |
| Write-LogMessage "New-DbaDirectory result: $($dirResult | Out-String)" -IsVerbose | |
| if (-not $dirAlreadyExisted) { | |
| Write-LogMessage "SUCCESS: New directory created and permissions set via dbatools" "Green" | |
| } else { | |
| Write-LogMessage "SUCCESS: Permissions verified for existing directory via dbatools" "Green" | |
| } | |
| } catch { | |
| if ($dirAlreadyExisted) { | |
| Write-LogMessage "WARNING: dbatools failed to set permissions for existing directory." "Yellow" | |
| Write-LogMessage " Attempting fallback to xp_cmdshell for permissions check." "Yellow" | |
| } else { | |
| Write-LogMessage "WARNING: dbatools failed to create directory or set permissions." "Yellow" | |
| Write-LogMessage " Attempting fallback to xp_cmdshell for directory creation and permissions." "Yellow" | |
| } | |
| # Fallback to xp_cmdshell method (from DANGER script) | |
| Write-LogMessage "Enabling xp_cmdshell for temporary directory creation" "Gray" | |
| Write-LogMessage "Fallback: Using xp_cmdshell to create directory and test permissions" -IsVerbose | |
| $enableCmd = @" | |
| EXEC sp_configure 'show advanced options', 1; RECONFIGURE; | |
| EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; | |
| "@ | |
| Write-LogMessage "Executing: Enable xp_cmdshell" -IsVerbose | |
| Invoke-DbaQuery -SqlInstance $SqlInstance -Query $enableCmd -Verbose:$Verbose | |
| $createDirQuery = @" | |
| DECLARE @cmd nvarchar(4000) = N'cmd /c if not exist "$targetPath" md "$targetPath"'; | |
| EXEC xp_cmdshell @cmd, NO_OUTPUT; | |
| -- Test write permissions | |
| DECLARE @testFile nvarchar(4000) = '$targetPath' + 'perm_test.tmp'; | |
| SET @cmd = N'cmd /c echo test > "' + @testFile + '"'; | |
| EXEC xp_cmdshell @cmd, NO_OUTPUT; | |
| -- Verify write succeeded | |
| CREATE TABLE #fx (FileExists int, IsDir int, ParentExists int); | |
| INSERT #fx EXEC master..xp_fileexist @testFile; | |
| IF NOT EXISTS (SELECT 1 FROM #fx WHERE FileExists = 1) | |
| THROW 50001, 'Write test failed. Check permissions for SQL service account.', 1; | |
| -- Cleanup | |
| SET @cmd = N'del "' + @testFile + '"'; | |
| EXEC xp_cmdshell @cmd, NO_OUTPUT; | |
| -- Disable xp_cmdshell | |
| EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE; | |
| EXEC sp_configure 'show advanced options', 0; RECONFIGURE; | |
| "@ | |
| Write-LogMessage "Executing: Directory creation and permission test via xp_cmdshell" -IsVerbose | |
| Invoke-DbaQuery -SqlInstance $SqlInstance -Query $createDirQuery -Verbose:$Verbose | |
| Write-LogMessage "SUCCESS: Directory created and permissions verified via xp_cmdshell" "Green" | |
| } | |
| } | |
| # Get service objects | |
| if ($WhatIf) { | |
| Write-LogMessage "[WhatIf] Would get SQL Server service objects" "Cyan" | |
| $engineSvc = $null | |
| $agentSvc = $null | |
| } else { | |
| Write-LogMessage "Getting SQL Server service objects" -IsVerbose | |
| $instanceForService = if($serviceName -eq 'MSSQLSERVER'){'MSSQLSERVER'}else{$serviceName.Replace('MSSQL$','')} | |
| Write-LogMessage "Looking for Engine service for instance: $instanceForService" -IsVerbose | |
| $engineSvc = Get-DbaService -ComputerName $env:COMPUTERNAME -Type Engine -InstanceName $instanceForService -Verbose:$Verbose | |
| Write-LogMessage "Engine service found: $($engineSvc.DisplayName) [$($engineSvc.State)]" -IsVerbose | |
| Write-LogMessage "Looking for Agent service for instance: $instanceForService" -IsVerbose | |
| $agentSvc = Get-DbaService -ComputerName $env:COMPUTERNAME -Type Agent -InstanceName $instanceForService -ErrorAction SilentlyContinue -Verbose:$Verbose | |
| if ($agentSvc) { | |
| Write-LogMessage "Agent service found: $($agentSvc.DisplayName) [$($agentSvc.State)]" -IsVerbose | |
| } else { | |
| Write-LogMessage "Agent service not found or not running" -IsVerbose | |
| } | |
| } | |
| # Capture original startup parameters for safety (we won't modify them) | |
| $origStartup = $null | |
| if ($WhatIf) { | |
| Write-LogMessage "[WhatIf] Would capture original startup parameters" "Cyan" | |
| } else { | |
| try { | |
| Write-LogMessage "Capturing original startup parameters" -IsVerbose | |
| $origStartup = Get-DbaStartupParameter -SqlInstance $SqlInstance -ErrorAction Stop -Verbose:$Verbose | |
| Write-LogMessage "Original startup parameters captured (will be preserved)" "Gray" | |
| # Log all startup parameter details | |
| if ($origStartup.MasterData) { | |
| Write-LogMessage " Master Data: $($origStartup.MasterData)" "Gray" | |
| } | |
| if ($origStartup.MasterLog) { | |
| Write-LogMessage " Master Log: $($origStartup.MasterLog)" "Gray" | |
| } | |
| if ($origStartup.ErrorLog) { | |
| Write-LogMessage " Error Log: $($origStartup.ErrorLog)" "Gray" | |
| } | |
| if ($origStartup.TraceFlags) { | |
| Write-LogMessage " Trace Flags: $($origStartup.TraceFlags -join ', ')" "Gray" | |
| } else { | |
| Write-LogMessage " Trace Flags: None" "Gray" | |
| } | |
| if ($origStartup.StartupParameters) { | |
| # Check for any additional startup parameters | |
| $additionalParams = $origStartup.StartupParameters | Where-Object { | |
| $_ -notmatch '^-[dleT]' -and $_ -ne '' | |
| } | |
| if ($additionalParams) { | |
| Write-LogMessage " Additional Parameters: $($additionalParams -join ', ')" "Gray" | |
| } | |
| } | |
| } catch { | |
| Write-LogMessage "Note: Could not capture startup parameters (may need elevation)" "Yellow" | |
| Write-LogMessage " This is OK - parameters won't be modified" "Gray" | |
| } | |
| } | |
| if (-not $WhatIf) { | |
| # Log migration approach | |
| Write-LogMessage "Migration approach: Standard restart method" "Gray" | |
| Write-LogMessage " - No trace flags will be added or modified" "Gray" | |
| Write-LogMessage " - No startup parameters will be modified" "Gray" | |
| Write-LogMessage " - Using normal SQL Server restarts to apply changes" "Gray" | |
| # Stop SQL services (without modifying startup parameters) | |
| $serviceStopTime = Get-Date | |
| Write-LogMessage "Stopping SQL Server services" "Yellow" | |
| Write-LogMessage "Service stop initiated at: $($serviceStopTime.ToString('HH:mm:ss'))" -IsVerbose | |
| # Stop SQL Agent first | |
| if ($agentSvc -and $agentSvc.State -eq 'Running') { | |
| Write-LogMessage "Stopping SQL Agent" "Gray" | |
| Write-LogMessage "Stopping service: $($agentSvc.DisplayName)" -IsVerbose | |
| $stopResult = Stop-DbaService -InputObject $agentSvc -ErrorAction SilentlyContinue -Verbose:$Verbose | |
| Write-LogMessage "Agent stop result: $($stopResult.Status)" -IsVerbose | |
| } | |
| # Stop SQL Server | |
| Write-LogMessage "Stopping SQL Server" "Gray" | |
| Write-LogMessage "Stopping service: $($engineSvc.DisplayName)" -IsVerbose | |
| $stopResult = Stop-DbaService -InputObject $engineSvc -Verbose:$Verbose | |
| Write-LogMessage "Engine stop result: $($stopResult.Status)" -IsVerbose | |
| $serviceStopDuration = (Get-Date) - $serviceStopTime | |
| Write-LogMessage "Services stopped in $($serviceStopDuration.TotalSeconds.ToString('F1')) seconds" -IsVerbose | |
| Start-Sleep -Seconds 3 | |
| } else { | |
| Write-LogMessage "[WhatIf] Would stop SQL Server services" "Cyan" | |
| Write-LogMessage "[WhatIf] Startup parameters would remain unchanged" "Cyan" | |
| } | |
| # Build ALTER commands | |
| $cmds = @() | |
| # Track data file count | |
| $dataFileIndex = 0 | |
| # Move all files to target | |
| foreach ($f in $files) { | |
| # Determine if we keep this file | |
| if ($f.type -eq 1) { | |
| # Always keep log files | |
| $keep = $true | |
| } elseif ($f.type -eq 0) { | |
| # For data files, keep based on target count | |
| $keep = ($dataFileIndex -lt $targetCount) | |
| $dataFileIndex++ | |
| } else { | |
| $keep = $false | |
| } | |
| if ($keep) { | |
| # Determine new filename | |
| if ($PreserveNames) { | |
| # Keep original filename | |
| $newName = Split-Path $f.physical_name -Leaf | |
| } else { | |
| # Use standardized naming | |
| $newName = switch -Regex ($f.name) { | |
| 'templog' { 'templog.ldf' } | |
| 'tempdev$|tempdb' { 'tempdb.mdf' } | |
| default { "$($f.name).ndf" } | |
| } | |
| } | |
| $newPath = Join-Path $targetPath $newName | |
| if ($BestPractices -and $f.type -eq 0) { | |
| # Best practices: Equal size, fixed growth (not percentage), unlimited max | |
| $cmds += "ALTER DATABASE tempdb MODIFY FILE (NAME=[$($f.name)], FILENAME='$newPath', SIZE=${sizePerFile}MB, FILEGROWTH=512MB, MAXSIZE=UNLIMITED)" | |
| } elseif ($BestPractices -and $f.type -eq 1) { | |
| # Log file: Fixed growth, not percentage | |
| $cmds += "ALTER DATABASE tempdb MODIFY FILE (NAME=[$($f.name)], FILENAME='$newPath', FILEGROWTH=512MB, MAXSIZE=UNLIMITED)" | |
| } else { | |
| $cmds += "ALTER DATABASE tempdb MODIFY FILE (NAME=[$($f.name)], FILENAME='$newPath')" | |
| } | |
| } else { | |
| $cmds += "ALTER DATABASE tempdb REMOVE FILE [$($f.name)]" | |
| } | |
| } | |
| # Show or execute commands | |
| Write-LogMessage "Generated $($cmds.Count) ALTER commands" | |
| $sql = "USE master`n" + ($cmds -join "`n") | |
| if ($WhatIf) { | |
| Write-LogMessage "[WhatIf] Would execute:" "Cyan" | |
| $cmds | ForEach-Object { Write-LogMessage " $_" "DarkGray" } | |
| Write-LogMessage "[WhatIf] Would restart SQL Server to apply changes" "Cyan" | |
| } else { | |
| try { | |
| # Start SQL Server normally to execute ALTER commands | |
| $alterStartTime = Get-Date | |
| Write-LogMessage "Starting SQL Server to apply changes" | |
| Write-LogMessage "ALTER phase started at: $($alterStartTime.ToString('HH:mm:ss'))" -IsVerbose | |
| Write-LogMessage " Using standard startup (no special flags)" "Gray" | |
| Write-LogMessage " Current startup parameters remain active" "Gray" | |
| Write-LogMessage "Starting service: $($engineSvc.DisplayName)" -IsVerbose | |
| $startResult = Start-DbaService -InputObject $engineSvc -Verbose:$Verbose | |
| Write-LogMessage "Engine start result: $($startResult.Status)" -IsVerbose | |
| # Wait for SQL to be ready | |
| $waited = 0 | |
| Write-LogMessage "Waiting for SQL Server to be ready..." "Gray" | |
| while ($waited -lt 30) { | |
| Write-LogMessage "Testing connection to $SqlInstance (attempt $($waited/2 + 1))" -IsVerbose | |
| $testResult = & sqlcmd -S $SqlInstance -E -Q "SELECT 1" 2>&1 | |
| Write-LogMessage "Connection test result: $($testResult -join ' ')" -IsVerbose | |
| if ($testResult -notlike "*error*" -and $testResult -notlike "*failed*") { | |
| Write-LogMessage " SQL Server is ready" "Gray" | |
| break | |
| } | |
| Start-Sleep -Seconds 2 | |
| $waited += 2 | |
| } | |
| if ($waited -ge 30) { | |
| Write-LogMessage "WARNING: SQL Server may not be fully ready" "Yellow" | |
| } | |
| # Execute ALTER commands | |
| Write-LogMessage "Executing ALTER commands" | |
| Write-LogMessage " Commands will take effect on next restart" "Gray" | |
| $tempSqlFile = "$env:TEMP\tempdb_move.sql" | |
| Write-LogMessage "Writing SQL commands to temporary file: $tempSqlFile" -IsVerbose | |
| $sql | Out-File $tempSqlFile | |
| Write-LogMessage "Executing SQL file via sqlcmd: $tempSqlFile" -IsVerbose | |
| $sqlResult = & sqlcmd -S $SqlInstance -E -i $tempSqlFile 2>&1 | |
| Write-LogMessage "sqlcmd execution result: $($sqlResult -join '; ')" -IsVerbose | |
| $alterDuration = (Get-Date) - $alterStartTime | |
| Write-LogMessage " ALTER commands executed successfully" "Gray" | |
| Write-LogMessage "ALTER phase completed in $($alterDuration.TotalSeconds.ToString('F1')) seconds" -IsVerbose | |
| # Stop SQL Server for cleanup | |
| Write-LogMessage "Stopping SQL Server for file cleanup" "Yellow" | |
| Write-LogMessage "Stopping service for cleanup: $($engineSvc.DisplayName)" -IsVerbose | |
| $stopResult = Stop-DbaService -InputObject $engineSvc -Verbose:$Verbose | |
| Write-LogMessage "Engine stop result for cleanup: $($stopResult.Status)" -IsVerbose | |
| Start-Sleep -Seconds 2 | |
| } catch { | |
| Write-LogMessage "Error occurred: $_" "Red" | |
| throw | |
| } | |
| } | |
| # Clean up old files | |
| if (-not $WhatIf) { | |
| Write-LogMessage "Cleaning up old files" "Yellow" | |
| Write-LogMessage " Old directories will be checked for orphaned TempDb files" "Gray" | |
| $oldPaths = $files | ForEach-Object { Split-Path $_.physical_name -Parent } | | |
| Select-Object -Unique | Where-Object { $_ -ne $targetPath.TrimEnd('\') } | |
| $deletedFiles = @() | |
| foreach ($path in $oldPaths) { | |
| Write-LogMessage "Checking for old TempDb files in: $path" -IsVerbose | |
| $oldFiles = Get-ChildItem "$path\temp*.*df" -ErrorAction SilentlyContinue | |
| if ($oldFiles) { | |
| foreach ($oldFile in $oldFiles) { | |
| Write-LogMessage "Deleting old file: $($oldFile.FullName) ($('{0:N1}' -f ($oldFile.Length/1MB)) MB)" "Gray" | |
| Remove-Item $oldFile.FullName -Force | |
| $deletedFiles += $oldFile.FullName | |
| } | |
| } else { | |
| Write-LogMessage "No old TempDb files found in: $path" -IsVerbose | |
| } | |
| } | |
| if ($deletedFiles.Count -gt 0) { | |
| Write-LogMessage "Successfully deleted $($deletedFiles.Count) old TempDb files" "Green" | |
| } else { | |
| Write-LogMessage "No old TempDb files needed cleanup" "Gray" | |
| } | |
| # Restart SQL Server normally | |
| $finalRestartTime = Get-Date | |
| Write-LogMessage "Starting SQL Server service normally (final restart)" "Yellow" | |
| Write-LogMessage "Final restart initiated at: $($finalRestartTime.ToString('HH:mm:ss'))" -IsVerbose | |
| Write-LogMessage " SQL Server will start with its existing startup parameters" "Gray" | |
| Write-LogMessage " Trace flags: $( if ($origStartup -and $origStartup.TraceFlags) { $origStartup.TraceFlags -join ', ' } else { 'None' } )" "Gray" | |
| Write-LogMessage " TempDb will be recreated in new location" "Green" | |
| Write-LogMessage "Starting service for final restart: $($engineSvc.DisplayName)" -IsVerbose | |
| $startResult = Start-DbaService -InputObject $engineSvc -Verbose:$Verbose | |
| Write-LogMessage "Final engine start result: $($startResult.Status)" -IsVerbose | |
| # Wait for SQL to be fully ready | |
| $waited = 0 | |
| Write-LogMessage "Waiting for SQL Server to be ready..." "Gray" | |
| while ($waited -lt 30) { | |
| $svc = Get-Service -Name $serviceName | |
| if ($svc.Status -eq 'Running') { | |
| Write-LogMessage " SQL Server is running" "Gray" | |
| break | |
| } | |
| Start-Sleep -Seconds 2 | |
| $waited += 2 | |
| } | |
| # Start SQL Agent | |
| if ($agentSvc) { | |
| Write-LogMessage "Starting SQL Agent service" "Gray" | |
| Write-LogMessage "Starting Agent service: $($agentSvc.DisplayName)" -IsVerbose | |
| $agentStartResult = Start-DbaService -InputObject $agentSvc -ErrorAction SilentlyContinue -Verbose:$Verbose | |
| Write-LogMessage "Agent start result: $($agentStartResult.Status)" -IsVerbose | |
| } | |
| # Verify startup parameters are preserved | |
| if ($origStartup) { | |
| try { | |
| Write-LogMessage "Verifying startup parameters are preserved" -IsVerbose | |
| $currentStartup = Get-DbaStartupParameter -SqlInstance $SqlInstance -ErrorAction Stop -Verbose:$Verbose | |
| $origFlags = if ($origStartup.TraceFlags) { $origStartup.TraceFlags -join ',' } else { 'None' } | |
| $currFlags = if ($currentStartup.TraceFlags) { $currentStartup.TraceFlags -join ',' } else { 'None' } | |
| $paramsChanged = $false | |
| # Check trace flags | |
| if ($origFlags -ne $currFlags) { | |
| Write-LogMessage "WARNING: Trace flags changed!" "Yellow" | |
| Write-LogMessage " Original: $origFlags" "Yellow" | |
| Write-LogMessage " Current: $currFlags" "Yellow" | |
| $paramsChanged = $true | |
| } | |
| # Check other critical parameters | |
| if ($origStartup.MasterData -ne $currentStartup.MasterData) { | |
| Write-LogMessage "WARNING: Master data path changed!" "Yellow" | |
| Write-LogMessage " Original: $($origStartup.MasterData)" "Yellow" | |
| Write-LogMessage " Current: $($currentStartup.MasterData)" "Yellow" | |
| $paramsChanged = $true | |
| } | |
| if ($origStartup.ErrorLog -ne $currentStartup.ErrorLog) { | |
| Write-LogMessage "WARNING: Error log path changed!" "Yellow" | |
| Write-LogMessage " Original: $($origStartup.ErrorLog)" "Yellow" | |
| Write-LogMessage " Current: $($currentStartup.ErrorLog)" "Yellow" | |
| $paramsChanged = $true | |
| } | |
| if (-not $paramsChanged) { | |
| Write-LogMessage "SUCCESS: Startup parameters preserved successfully" "Green" | |
| if ($origFlags -ne 'None') { | |
| Write-LogMessage " Trace flags maintained: $origFlags" "Gray" | |
| } | |
| } | |
| } catch { | |
| Write-LogMessage "Note: Could not verify startup parameters (may need elevation)" "Gray" | |
| } | |
| } | |
| } else { | |
| Write-LogMessage "[WhatIf] Would clean up old TempDb files" "Cyan" | |
| Write-LogMessage "[WhatIf] Would restart SQL Server and dependent services" "Cyan" | |
| Write-LogMessage "[WhatIf] Startup parameters would remain unchanged" "Cyan" | |
| if ($SetDelayedStart) { | |
| Write-LogMessage "[WhatIf] Would set SQL services to delayed-auto start" "Cyan" | |
| } else { | |
| Write-LogMessage "[WhatIf] Service startup types would remain unchanged" "Cyan" | |
| } | |
| } | |
| # Verify | |
| if (-not $WhatIf) { | |
| Write-LogMessage "Verifying migration success" -IsVerbose | |
| $newFiles = Invoke-DbaQuery -SqlInstance $SqlInstance -Query $query -Verbose:$Verbose | |
| Write-LogMessage "Found $($newFiles.Count) TempDb files after migration" -IsVerbose | |
| $success = ($newFiles | Where-Object { $_.physical_name -notlike "$targetPath*" }).Count -eq 0 | |
| if ($success) { | |
| $totalDuration = (Get-Date) - $scriptStartTime | |
| Write-LogMessage "SUCCESS: Migration Complete!" "Green" | |
| Write-LogMessage "Total migration time: $($totalDuration.TotalMinutes.ToString('F1')) minutes" "Cyan" | |
| # Log migration method summary | |
| Write-LogMessage "Migration Method Summary:" "Cyan" | |
| Write-LogMessage " Method: Standard SQL Server restart" "Gray" | |
| Write-LogMessage " Trace flags used: None (clean restart method)" "Gray" | |
| Write-LogMessage " Startup parameters modified: No" "Gray" | |
| Write-LogMessage " Total restarts: 2 (for ALTER and cleanup)" "Gray" | |
| Write-LogMessage "New TempDb configuration:" | |
| $newFiles | ForEach-Object { | |
| Write-LogMessage " $($_.name) -> $($_.physical_name)" "Green" | |
| } | |
| # Set delayed start for ephemeral storage (only if requested) | |
| if ($SetDelayedStart) { | |
| Write-LogMessage "Setting services to delayed-auto start (ephemeral storage optimization)" "Cyan" | |
| $agentService = if ($serviceName -eq 'MSSQLSERVER') { 'SQLSERVERAGENT' } else { "SQLAgent`$$instanceName" } | |
| Write-LogMessage "Setting $serviceName to delayed-auto start" -IsVerbose | |
| $scResult = & sc.exe config $serviceName start= delayed-auto 2>&1 | |
| Write-LogMessage "sc.exe result for $serviceName`: $($scResult -join '; ')" -IsVerbose | |
| Write-LogMessage "Setting $agentService to delayed-auto start" -IsVerbose | |
| $scAgentResult = & sc.exe config $agentService start= delayed-auto 2>&1 | |
| Write-LogMessage "sc.exe result for $agentService`: $($scAgentResult -join '; ')" -IsVerbose | |
| Write-LogMessage "Services configured for delayed-auto start" "Green" | |
| } else { | |
| Write-LogMessage "Service startup types left unchanged (use -SetDelayedStart to modify)" "Gray" | |
| } | |
| Write-LogMessage "`nSUCCESS: TempDb relocated to $targetPath" "Green" | |
| # Run best practices check if available | |
| if ($BestPractices) { | |
| Write-LogMessage "`nRunning TempDb best practices check..." "Cyan" | |
| try { | |
| Write-LogMessage "Executing Test-DbaTempDbConfig" -IsVerbose | |
| $bpCheck = Test-DbaTempDbConfig -SqlInstance $SqlInstance -Verbose:$Verbose | |
| $failures = $bpCheck | Where-Object { -not $_.IsBestPractice } | |
| if ($failures) { | |
| Write-LogMessage "Best practice warnings:" "Yellow" | |
| $failures | ForEach-Object { | |
| Write-LogMessage " - $($_.Rule): $($_.Notes)" "Yellow" | |
| } | |
| } else { | |
| Write-LogMessage "All TempDb best practices met!" "Green" | |
| } | |
| } catch { | |
| Write-LogMessage "Could not run best practices check" "Gray" | |
| } | |
| } | |
| } else { | |
| Write-LogMessage "Migration incomplete - some files not moved" "Red" | |
| $newFiles | Where-Object { $_.physical_name -notlike "$targetPath*" } | ForEach-Object { | |
| Write-LogMessage " FAILED: $($_.name) still at $($_.physical_name)" "Red" | |
| } | |
| } | |
| } else { | |
| Write-LogMessage "[WhatIf] Would verify migration success" "Cyan" | |
| if ($SetDelayedStart) { | |
| Write-LogMessage "[WhatIf] Would set services to delayed-auto start" "Cyan" | |
| } | |
| Write-LogMessage "[WhatIf] TempDb would be moved to: $targetPath" "Cyan" | |
| } | |
| } catch { | |
| Write-LogMessage "FATAL ERROR: $($_.Exception.Message)" "Red" | |
| Write-LogMessage "Migration failed and may be in an inconsistent state" "Red" | |
| Write-LogMessage "Check SQL Server status and TempDb file locations manually" "Yellow" | |
| throw | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment