functions/ExportImportSqlConfig.ps1
|
Function Export-SqlSpConfigure { <# .SYNOPSIS Exports advanced sp_configure global configuration options to sql file. .EXAMPLE $outputfile = Export-SqlSpConfigure $sourceserver -Path C:\temp\sp_configure.sql .OUTPUTS File to disk, and string path. #> [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess = $true)] param ( [Parameter(Mandatory = $true)] [ValidateNotNullOrEmpty()] [Alias("ServerInstance","SqlInstance")] [object]$SqlServer, [string]$Path, [System.Management.Automation.PSCredential]$SqlCredential ) BEGIN { $server = Connect-SqlServer $SqlServer $SqlCredential if ($server.versionMajor -lt 9) { "Windows 2000 not supported for sp_configure export."; break } if ($path.length -eq 0) { $timenow = (Get-Date -uformat "%m%d%Y%H%M%S") $mydocs = [Environment]::GetFolderPath('MyDocuments') $path = "$mydocs\$($server.name.replace('\', '$'))-$timenow-sp_configure.sql" } } PROCESS { try { Set-Content -Path $path "EXEC sp_configure 'show advanced options' , 1; RECONFIGURE WITH OVERRIDE" } catch { throw "Can't write to $path" } $server.Configuration.ShowAdvancedOptions.ConfigValue = $true $server.Configuration.Alter($true) foreach ($sourceprop in $server.Configuration.Properties) { $displayname = $sourceprop.DisplayName $configvalue = $sourceprop.ConfigValue Add-Content -Path $path "EXEC sp_configure '$displayname' , $configvalue;" } Add-Content -Path $path "EXEC sp_configure 'show advanced options' , 0;" Add-Content -Path $Path "RECONFIGURE WITH OVERRIDE" $server.Configuration.ShowAdvancedOptions.ConfigValue = $false $server.Configuration.Alter($true) return $path } END { $server.ConnectionContext.Disconnect() If ($Pscmdlet.ShouldProcess("console", "Showing finished message")) { Write-Output "Server configuration export finished" } } } Function Import-SqlSpConfigure { <# .SYNOPSIS Updates sp_configure settings on destination server. .EXAMPLE Import-SqlSpConfigure sqlserver sqlcluster $SourceSqlCredential $DestinationSqlCredential .EXAMPLE Import-SqlSpConfigure -SqlServer sqlserver -Path .\spconfig.sql -SqlCredential $SqlCredential .OUTPUTS $true if success $false if failure #> [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess = $true)] param ( [object]$Source, [object]$Destination, [System.Management.Automation.PSCredential]$SourceSqlCredential, [System.Management.Automation.PSCredential]$DestinationSqlCredential, [Alias("ServerInstance","SqlInstance")] [object]$SqlServer, [string]$Path, [System.Management.Automation.PSCredential]$SqlCredential, [switch]$Force ) BEGIN { if ($Path.length -eq 0) { $sourceserver = Connect-SqlServer -SqlServer $Source -SqlCredential $SourceSqlCredential $destserver = Connect-SqlServer -SqlServer $Destination -SqlCredential $DestinationSqlCredential $source = $sourceserver.DomainInstanceName $destination = $destserver.DomainInstanceName } else { $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential if ((Test-Path $Path) -eq $false) { throw "File Not Found" } } } PROCESS { if ($Path.length -eq 0) { If ($Pscmdlet.ShouldProcess($destination, "Export sp_configure")) { $sqlfilename = Export-SqlSpConfigure $sourceserver } if ($sourceserver.versionMajor -ne $destserver.versionMajor -and $force -eq $false) { Write-Warning "Source SQL Server major version and Destination SQL Server major version must match for sp_configure migration. Use -Force to override this precaution or check the exported sql file, $sqlfilename, and run manually." return } If ($Pscmdlet.ShouldProcess($destination, "Execute sp_configure")) { $sourceserver.Configuration.ShowAdvancedOptions.ConfigValue = $true $sourceserver.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") | Out-Null $destserver.Configuration.ShowAdvancedOptions.ConfigValue = $true $destserver.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") | Out-Null $destprops = $destserver.Configuration.Properties foreach ($sourceprop in $sourceserver.Configuration.Properties) { $displayname = $sourceprop.DisplayName $destprop = $destprops | where-object{ $_.Displayname -eq $displayname } if ($destprop -ne $null) { try { $destprop.configvalue = $sourceprop.configvalue $destserver.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") | Out-Null Write-Output "updated $($destprop.displayname) to $($sourceprop.configvalue)" } catch { Write-Error "Could not $($destprop.displayname) to $($sourceprop.configvalue). Feature may not be supported." } } } try { $destserver.Configuration.Alter() } catch { $needsrestart = $true } $sourceserver.Configuration.ShowAdvancedOptions.ConfigValue = $false $sourceserver.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") | Out-Null $destserver.Configuration.ShowAdvancedOptions.ConfigValue = $false $destserver.ConnectionContext.ExecuteNonQuery("RECONFIGURE WITH OVERRIDE") | Out-Null if ($needsrestart -eq $true) { Write-Warning "Some configuration options will be updated once SQL Server is restarted." } else { Write-Output "Configuration option has been updated." } } If ($Pscmdlet.ShouldProcess($destination, "Removing temp file")) { Remove-Item $sqlfilename -ErrorAction SilentlyContinue } } else { If ($Pscmdlet.ShouldProcess($destination, "Importing sp_configure from $Path")) { $server.Configuration.ShowAdvancedOptions.ConfigValue = $true $sql = Get-Content $Path foreach ($line in $sql) { try { $server.ConnectionContext.ExecuteNonQuery($line) | Out-Null; Write-Output "Successfully executed $line" } catch { Write-Error "$line failed. Feature may not be supported." } } $server.Configuration.ShowAdvancedOptions.ConfigValue = $false Write-Warning "Some configuration options will be updated once SQL Server is restarted." } } } END { if ($Path.length -gt 0) { $server.ConnectionContext.Disconnect() } else { $sourceserver.ConnectionContext.Disconnect() $destserver.ConnectionContext.Disconnect() } If ($Pscmdlet.ShouldProcess("console", "Showing finished message")) { Write-Output "SQL Server configuration options migration finished" } } } |