Public/Export-sqmServerConfiguration.ps1
|
<#
.SYNOPSIS Exports all SQL Server configuration settings to a JSON snapshot file. .DESCRIPTION This function reads comprehensive configuration data from a SQL Server instance and saves it as a JSON snapshot with timestamp. The snapshot can be used for documentation, comparison, or rollback purposes. Captured settings include: - sp_configure values (MaxServerMemory, MAXDOP, xp_cmdshell, etc.) - Instance properties (BackupDirectory, DefaultFile, DefaultLog, Collation, etc.) - Service configuration (SQL Server, Agent, SSRS, SSIS start mode and accounts) - Startup parameters (registry trace flags, etc.) - TempDB configuration - Database Mail profiles (if configured) - Linked Servers - Database overview (optional, slower) .PARAMETER SqlInstance Target SQL Server instance (default: $env:COMPUTERNAME). .PARAMETER SqlCredential Optional alternative credentials (PSCredential object). .PARAMETER OutputPath Path where JSON snapshot will be saved. Default: $env:ProgramData\sqmSQLTool\Snapshots .PARAMETER Label Optional descriptive label for this snapshot (e.g., "before MaxMemory change"). Included in the JSON metadata. .PARAMETER IncludeDatabases When set, includes database-level settings (slower operation). .PARAMETER NoOpen Suppresses automatically opening the generated HTML report after the export completes. .PARAMETER EnableException Switch to allow exceptions to pass through (default: errors logged as warnings). .OUTPUTS [PSCustomObject] with properties: - SnapshotPath: Full path to saved JSON file - ReportPath: Full path to the saved HTML report (same folder/basename, .html) - a customer-presentable summary of everything captured, opened automatically unless -NoOpen - Timestamp: When snapshot was created (ISO 8601 format) - ItemCount: Total settings captured - Categories: List of captured categories - Status: 'Success' or 'Partial' (if some categories failed) .EXAMPLE # Create a snapshot before making configuration changes $snap = Export-sqmServerConfiguration -SqlInstance "SQL01" -Label "before MaxMemory change" Write-Host "Snapshot saved to: $($snap.SnapshotPath)" .EXAMPLE # Export with custom output path Export-sqmServerConfiguration -SqlInstance "SQL01" ` -OutputPath "C:\Backups\SQLSnapshots" ` -Label "production-baseline" .EXAMPLE # Full export including databases Export-sqmServerConfiguration -SqlInstance "SQL01" ` -IncludeDatabases ` -Label "complete-inventory" .NOTES Requires dbatools module and appropriate SQL Server permissions. Registry parameter reading requires local admin rights. #> function Export-sqmServerConfiguration { [CmdletBinding(SupportsShouldProcess = $false)] param ( [Parameter(Mandatory = $false, Position = 0)] [string]$SqlInstance, [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory = $false)] [string]$OutputPath, [Parameter(Mandatory = $false)] [string]$Label, [Parameter(Mandatory = $false)] [switch]$IncludeDatabases, [Parameter(Mandatory = $false)] [switch]$NoOpen, [Parameter(Mandatory = $false)] [switch]$EnableException ) begin { $functionName = $MyInvocation.MyCommand.Name $timestampIso = Get-Date -Format 'o' $timestampFile = Get-Date -Format 'yyyyMMdd_HHmmss' # Default SqlInstance if (-not $SqlInstance) { $SqlInstance = $env:COMPUTERNAME } Invoke-sqmLogging -Message "Starte $functionName fuer Instanz: $SqlInstance" ` -FunctionName $functionName -Level "INFO" # Default OutputPath if (-not $OutputPath) { $configOutput = Get-sqmConfig -Key 'OutputPath' $OutputPath = if ($configOutput) { Join-Path $configOutput 'Snapshots' } else { Join-Path $env:ProgramData 'sqmSQLTool\Snapshots' } } # Ensure output directory exists if (-not (Test-Path $OutputPath)) { try { $null = New-Item -ItemType Directory -Path $OutputPath -Force -ErrorAction Stop Invoke-sqmLogging -Message "Output directory created: $OutputPath" ` -FunctionName $functionName -Level "DEBUG" } catch { $msg = "Fehler beim Erstellen des Output-Verzeichnisses: $($_.Exception.Message)" Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw } return $null } } # Check for dbatools if (-not (Get-Module -ListAvailable -Name dbatools)) { $msg = "dbatools-Modul nicht gefunden. Bitte installieren: Install-Module dbatools" Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR" throw $msg } } process { try { $allSettings = @{} $capturedCategories = @() # ======================================================================== # 1. Connect to SQL Server # ======================================================================== Invoke-sqmLogging -Message "Verbinde mit SQL Server: $SqlInstance" ` -FunctionName $functionName -Level "DEBUG" $serverParams = @{ SqlInstance = $SqlInstance ErrorAction = 'Stop' } if ($SqlCredential) { $serverParams['SqlCredential'] = $SqlCredential } if ($EnableException) { $serverParams['EnableException'] = $true } $server = Connect-DbaInstance @serverParams if (-not $server) { throw "Konnte keine Verbindung mit $SqlInstance herstellen" } # Get server name and instance name for output filename $serverName = $server.ComputerName $instanceName = $server.InstanceName if (-not $instanceName) { $instanceName = 'MSSQLSERVER' } # ======================================================================== # 2. sp_configure (Configuration Settings) # ======================================================================== try { Invoke-sqmLogging -Message "Lese sp_configure Einstellungen..." ` -FunctionName $functionName -Level "DEBUG" # $server.Configuration selbst ist ein einzelnes SMO-Objekt (kein Enumerable) - # die eigentliche Liste der ~95 sp_configure-Werte liegt unter .Properties. # Ein direktes "foreach ($x in $server.Configuration)" liefert nur dieses eine # leere Objekt und wuerde die Konfiguration praktisch nicht erfassen. # .ConfigName ist auf manchen SMO-Versionen leer - DisplayName als Fallback, # da Set-DbaSpConfigure -Name beide Formen akzeptiert. $spConfigValues = @() foreach ($config in $server.Configuration.Properties) { $spConfigValues += [PSCustomObject]@{ Name = $config.DisplayName ConfigName = if ($config.ConfigName) { $config.ConfigName } else { $config.DisplayName } Minimum = $config.Minimum Maximum = $config.Maximum RunValue = $config.RunValue ConfigValue = $config.ConfigValue IsDynamic = $config.IsDynamic Description = $config.Description } } $allSettings['SpConfigure'] = @{ count = $spConfigValues.Count items = $spConfigValues } $capturedCategories += 'SpConfigure' Invoke-sqmLogging -Message "sp_configure: $($spConfigValues.Count) Einstellungen erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen von sp_configure: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } # ======================================================================== # 3. Instance Properties (SMO) # ======================================================================== try { Invoke-sqmLogging -Message "Lese Instance Properties..." ` -FunctionName $functionName -Level "DEBUG" $instanceProps = [PSCustomObject]@{ ComputerName = $server.ComputerName InstanceName = $server.InstanceName Edition = $server.Edition VersionString = $server.VersionString ProductLevel = $server.ProductLevel ProductUpdateLevel = $server.ProductUpdateLevel HostPlatform = $server.HostPlatform IsClustered = $server.IsClustered IsHadrEnabled = $server.IsHadrEnabled Collation = $server.Collation LoginMode = $server.LoginMode BackupDirectory = $server.BackupDirectory DefaultFile = $server.DefaultFile DefaultLog = $server.DefaultLog MasterDBPath = $server.MasterDBPath ErrorLogPath = $server.ErrorLogPath } $allSettings['InstanceProperties'] = $instanceProps $capturedCategories += 'InstanceProperties' Invoke-sqmLogging -Message "Instance Properties erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen von Instance Properties: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } # ======================================================================== # 4. Service Configuration # ======================================================================== try { Invoke-sqmLogging -Message "Lese Service-Konfiguration..." ` -FunctionName $functionName -Level "DEBUG" $services = Get-DbaService -ComputerName $serverName -ErrorAction SilentlyContinue $serviceList = @() if ($services) { foreach ($svc in $services) { $serviceList += [PSCustomObject]@{ ServiceName = $svc.ServiceName ServiceType = $svc.ServiceType State = $svc.State StartMode = $svc.StartMode ProcessId = $svc.ProcessId InstanceName = $svc.InstanceName ServiceAccount = $svc.ServiceAccount } } } $allSettings['Services'] = @{ count = $serviceList.Count items = $serviceList } $capturedCategories += 'Services' Invoke-sqmLogging -Message "Services: $($serviceList.Count) Dienste erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen der Services: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } # ======================================================================== # 5. TempDB Configuration # ======================================================================== try { Invoke-sqmLogging -Message "Lese TempDB-Konfiguration..." ` -FunctionName $functionName -Level "DEBUG" $tempdbFiles = Get-DbaDbFile -SqlInstance $server -Database 'tempdb' -ErrorAction SilentlyContinue $fileList = @() if ($tempdbFiles) { foreach ($file in $tempdbFiles) { $fileList += [PSCustomObject]@{ LogicalName = $file.LogicalName PhysicalName = $file.PhysicalName Type = $file.Type Size = $file.Size UsedSpace = $file.UsedSpace Growth = $file.Growth GrowthType = $file.GrowthType IsPercentGrowth = $file.IsPercentGrowth } } } $allSettings['TempDb'] = @{ fileCount = $fileList.Count files = $fileList } $capturedCategories += 'TempDb' Invoke-sqmLogging -Message "TempDB: $($fileList.Count) Dateien erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen von TempDB: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } # ======================================================================== # 6. Database Mail Configuration # ======================================================================== try { Invoke-sqmLogging -Message "Lese Database Mail-Konfiguration..." ` -FunctionName $functionName -Level "DEBUG" $mailProfiles = Get-DbaDbMailProfile -SqlInstance $server -ErrorAction SilentlyContinue $profileList = @() if ($mailProfiles) { foreach ($profile in $mailProfiles) { $profileList += [PSCustomObject]@{ Name = $profile.Name Description = $profile.Description IsPublic = $profile.IsPublic IsDefault = $profile.IsDefault } } } $allSettings['DatabaseMail'] = @{ profileCount = $profileList.Count profiles = $profileList } $capturedCategories += 'DatabaseMail' Invoke-sqmLogging -Message "Database Mail: $($profileList.Count) Profile erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen von Database Mail: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } # ======================================================================== # 7. Linked Servers # ======================================================================== try { Invoke-sqmLogging -Message "Lese Linked Server-Konfiguration..." ` -FunctionName $functionName -Level "DEBUG" $linkedServers = Get-DbaLinkedServer -SqlInstance $server -ErrorAction SilentlyContinue $linkedServerList = @() if ($linkedServers) { foreach ($ls in $linkedServers) { $linkedServerList += [PSCustomObject]@{ Name = $ls.Name DataSource = $ls.DataSource Provider = $ls.Provider ProviderString = $ls.ProviderString Catalog = $ls.Catalog IsRemoteLogin = $ls.IsRemoteLogin LazySchemaValidation = $ls.LazySchemaValidation } } } $allSettings['LinkedServers'] = @{ count = $linkedServerList.Count items = $linkedServerList } $capturedCategories += 'LinkedServers' Invoke-sqmLogging -Message "Linked Servers: $($linkedServerList.Count) erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen von Linked Servers: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } # ======================================================================== # 8. Database Overview (optional) # ======================================================================== if ($IncludeDatabases) { try { Invoke-sqmLogging -Message "Lese Datenbank-Uebersicht..." ` -FunctionName $functionName -Level "DEBUG" $databases = Get-DbaDatabase -SqlInstance $server -ErrorAction SilentlyContinue $dbList = @() if ($databases) { foreach ($db in $databases) { $dbList += [PSCustomObject]@{ Name = $db.Name Owner = $db.Owner CreateDate = $db.CreateDate RecoveryModel = $db.RecoveryModel CompatibilityLevel = $db.CompatibilityLevel Status = $db.Status IsSystemObject = $db.IsSystemObject AutoClose = $db.AutoClose AutoShrink = $db.AutoShrink Trustworthy = $db.Trustworthy } } } $allSettings['Databases'] = @{ count = $dbList.Count items = $dbList } $capturedCategories += 'Databases' Invoke-sqmLogging -Message "Datenbanken: $($dbList.Count) erfasst" ` -FunctionName $functionName -Level "DEBUG" } catch { Invoke-sqmLogging -Message "Fehler beim Lesen von Datenbanken: $($_.Exception.Message)" ` -FunctionName $functionName -Level "WARN" } } # ======================================================================== # 9. Build complete snapshot object # ======================================================================== $snapshot = [PSCustomObject]@{ Metadata = [PSCustomObject]@{ ExportDate = $timestampIso ExportedBy = $env:USERNAME ComputerName = $serverName InstanceName = $instanceName Label = if ($Label) { $Label } else { "" } Categories = $capturedCategories } Configuration = $allSettings } # ======================================================================== # 10. Save to JSON # ======================================================================== $filename = "$($serverName)_$($instanceName)_$($timestampFile).json" $filepath = Join-Path $OutputPath $filename Invoke-sqmLogging -Message "Speichere Snapshot nach: $filepath" ` -FunctionName $functionName -Level "DEBUG" $jsonContent = $snapshot | ConvertTo-Json -Depth 10 -ErrorAction Stop Set-Content -Path $filepath -Value $jsonContent -Encoding UTF8 -ErrorAction Stop Invoke-sqmLogging -Message "Snapshot erfolgreich gespeichert: $filepath" ` -FunctionName $functionName -Level "INFO" # ======================================================================== # 10b. HTML-Bericht bauen (kundenpraesentabel, gleiche Kategorien wie das JSON) # ======================================================================== function _Enc($s) { if ($null -eq $s) { return '' } return ([string]$s) -replace '&', '&' -replace '<', '<' -replace '>', '>' } function _BuildTable($items, [string[]]$Columns) { $rows = @($items) if ($rows.Count -eq 0) { return "<p style='color:#94a8c0;'>Keine Eintraege.</p>" } $headerHtml = ($Columns | ForEach-Object { "<th>$(_Enc $_)</th>" }) -join '' $rowsHtml = ($rows | ForEach-Object { $item = $_ $cells = ($Columns | ForEach-Object { "<td>$(_Enc $item.$_)</td>" }) -join '' "<tr>$cells</tr>" }) -join "`n" return "<table><thead><tr>$headerHtml</tr></thead><tbody>$rowsHtml</tbody></table>" } function _BuildKVTable($obj) { if (-not $obj) { return "<p style='color:#94a8c0;'>Keine Daten.</p>" } $rows = ($obj.PSObject.Properties | ForEach-Object { "<tr><td>$(_Enc $_.Name)</td><td>$(_Enc $_.Value)</td></tr>" }) -join "`n" return "<table><tbody>$rows</tbody></table>" } $metaTable = _BuildKVTable ([PSCustomObject]@{ Instanz = "$serverName\$instanceName" Label = if ($Label) { $Label } else { '-' } Exportiert = $timestampIso Von = $env:USERNAME }) $h2Style = "color:#5dade2;margin-top:26px;" $bodyHtml = "<h2 style='$h2Style'>Zusammenfassung</h2>$metaTable" $bodyHtml += "<h2 style='$h2Style'>sp_configure ($($spConfigValues.Count))</h2>" + (_BuildTable $spConfigValues @('Name', 'RunValue', 'ConfigValue', 'Minimum', 'Maximum', 'IsDynamic')) $bodyHtml += "<h2 style='$h2Style'>Instance Properties</h2>" + (_BuildKVTable $instanceProps) $bodyHtml += "<h2 style='$h2Style'>Services ($($serviceList.Count))</h2>" + (_BuildTable $serviceList @('ServiceName', 'ServiceType', 'State', 'StartMode', 'ServiceAccount')) $bodyHtml += "<h2 style='$h2Style'>TempDb-Dateien ($($fileList.Count))</h2>" + (_BuildTable $fileList @('LogicalName', 'Type', 'Size', 'Growth', 'GrowthType', 'IsPercentGrowth')) $bodyHtml += "<h2 style='$h2Style'>Database Mail Profile ($($profileList.Count))</h2>" + (_BuildTable $profileList @('Name', 'Description', 'IsPublic', 'IsDefault')) $bodyHtml += "<h2 style='$h2Style'>Linked Servers ($($linkedServerList.Count))</h2>" + (_BuildTable $linkedServerList @('Name', 'DataSource', 'Provider', 'Catalog')) if ($IncludeDatabases) { $bodyHtml += "<h2 style='$h2Style'>Datenbanken ($($dbList.Count))</h2>" + (_BuildTable $dbList @('Name', 'RecoveryModel', 'CompatibilityLevel', 'Status', 'AutoClose', 'AutoShrink', 'Trustworthy')) } $htmlFilename = "$($serverName)_$($instanceName)_$($timestampFile).html" $htmlFilepath = Join-Path $OutputPath $htmlFilename $html = ConvertTo-sqmHtmlReport -Title "Server Configuration Report - $serverName\$instanceName" ` -Subtitle "Label: $(if ($Label) { $Label } else { '-' }) | Exportiert: $timestampIso" -BodyHtml $bodyHtml Set-Content -Path $htmlFilepath -Value $html -Encoding UTF8 -ErrorAction Stop Invoke-sqmLogging -Message "HTML-Bericht gespeichert: $htmlFilepath" -FunctionName $functionName -Level "INFO" Invoke-sqmOpenReport -HtmlFile $htmlFilepath -NoOpen:$NoOpen # Count total items captured $totalItems = $allSettings.Values | ForEach-Object { if ($_ -is [hashtable]) { $_.count + ($_.items | Measure-Object).Count } else { 1 } } | Measure-Object -Sum | Select-Object -ExpandProperty Sum # Return result object return [PSCustomObject]@{ SnapshotPath = $filepath ReportPath = $htmlFilepath Timestamp = $timestampIso ItemCount = $totalItems Categories = $capturedCategories Status = 'Success' } } catch { $msg = "Fehler bei Export-sqmServerConfiguration: $($_.Exception.Message)" Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw } else { Write-Error $msg return $null } } } end { Invoke-sqmLogging -Message "$functionName abgeschlossen" ` -FunctionName $functionName -Level "INFO" } } |