sqmSQLTool › Commands › Invoke-sqmConfigRollback

Invoke-sqmConfigRollback Configuration

Reads a JSON snapshot previously exported by Export-sqmServerConfiguration and restores selected settings back to a SQL Server instance. Supports four categories: SpConfigure (sp_configure values), InstanceProperties (BackupDirectory, DefaultFile, DefaultLog), Services (StartMode via CIM), and DatabaseMail (info-only). Full -WhatIf support — no changes are applied during a dry run.

Execution Flow

START Load snapshot: Get-Content $SnapshotPath -Raw | ConvertFrom-Json Validate: Metadata + Configuration keys must exist → error / return @() otherwise dbatools available? throw; return Connect-DbaInstance $SqlInstance [-SqlCredential] Category: SpConfigure foreach config in snapshot.SpConfigure.items currentValue == snapshotValue → Status=Skipped (no change) WhatIf → Status=Restored (no write) | else: $config.ConfigValue = $snapshotValue; Alter() Category: InstanceProperties foreach: BackupDirectory | DefaultFile | DefaultLog $server.$prop = $snapshotValue; $server.Alter() Category: Services (StartMode only) Get-DbaService -ComputerName $server.ComputerName Invoke-CimMethod SetStartMode (Auto=2, Manual=3, Disabled=4) | ReturnValue≠0 → Failed Service not found → Status=Skipped Category: DatabaseMail Info only — Status=Skipped + message "must be restored manually" Return PSCustomObject[] [ Setting, Category, OldValue, NewValue, Status, Message ] DONE

Synopsis

The companion restore function for Export-sqmServerConfiguration. Reads the JSON snapshot file and processes up to four categories in order. For SpConfigure, each setting is compared against the current value — identical values are skipped with Status=Skipped; differing values are changed via $config.ConfigValue = …; Alter(). Instance properties are applied the same way via SMO. Service StartMode uses Invoke-CimMethod SetStartMode with the numeric mapping (Auto=2, Manual=3, Disabled=4). Database Mail profiles are always listed as Skipped with a note that manual restoration is required.

Use -WhatIf first to preview all changes. The output table shows OldValue vs. NewValue for every setting regardless of whether changes are applied.
Some sp_configure changes (e.g., max degree of parallelism) take effect immediately; others (e.g., max worker threads) require a SQL Server restart. Service changes require local administrator rights on the server.

Syntax

Invoke-sqmConfigRollback
    [-SqlInstance <String>]             # default: $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    -SnapshotPath <String>              # required; path to JSON snapshot file
    [-Category <SpConfigure|InstanceProperties|Services|DatabaseMail|All>]
    [-Force]                             # skip confirmation
    [-EnableException]
    [-WhatIf]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMETarget SQL Server instance name.
-SqlCredentialPSCredential—SQL authentication credentials. Omit for Windows Authentication.
-SnapshotPathString—Required. Full path to the JSON snapshot created by Export-sqmServerConfiguration. Validated with ValidateScript.
-CategoryString[]AllLimit rollback scope: SpConfigure, InstanceProperties, Services, DatabaseMail, or All.
-ForceSwitch$falseSkip confirmation prompt and apply changes immediately.
-EnableExceptionSwitch$falsePropagate exceptions instead of logging as warnings.

Return Value

PropertyDescription
SettingSetting name (sp_configure key, property name, service name, or mail profile name).
CategorySpConfigure, InstanceProperties, Services, or DatabaseMail.
OldValueCurrent value on the server before rollback.
NewValueValue from the snapshot.
StatusRestored — changed successfully; Skipped — already matches or info-only; Failed — error during apply.
MessageDetailed status description.

Examples

# Preview all changes (dry run)
Invoke-sqmConfigRollback -SqlInstance "SQL01" `
    -SnapshotPath "C:\Snapshots\SQL01_MSSQLSERVER_20260602_143022.json" `
    -WhatIf

# Apply rollback with confirmation prompt
Invoke-sqmConfigRollback -SqlInstance "SQL01" `
    -SnapshotPath "C:\Snapshots\SQL01_MSSQLSERVER_20260602_143022.json"

# Force rollback without confirmation
Invoke-sqmConfigRollback -SqlInstance "SQL01" `
    -SnapshotPath "C:\Snapshots\SQL01_MSSQLSERVER_20260602_143022.json" -Force

# Rollback only sp_configure settings
Invoke-sqmConfigRollback -SqlInstance "SQL01" `
    -SnapshotPath "C:\Snapshots\SQL01_MSSQLSERVER_20260602_143022.json" `
    -Category 'SpConfigure' -Force

# Check result — see which settings were actually changed
$r = Invoke-sqmConfigRollback -SqlInstance "SQL01" `
    -SnapshotPath "C:\Snapshots\SQL01_20260602.json" -Force
$r | Where-Object Status -eq 'Restored' | Format-Table Setting, OldValue, NewValue