commands

^

Set-DbaSpConfigure

Author Nic Cain, https://sirsql.net/
Availability Windows, Linux, macOS

 

Synopsis

Changes the server level system configuration (sys.configuration/sp_configure) value for a given configuration

Description

This function changes the configured value for sp_configure settings. If the setting is dynamic this setting will be used, otherwise the user will be warned that a restart of SQL is required. This is designed to be safe and will not allow for configurations to be set outside of the defined configuration min and max values. While it is possible to set below the min, or above the max this can cause serious problems with SQL Server (including startup failures), and so is not permitted.

Syntax

Set-DbaSpConfigure
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Value] <Int32>]
    [[-Name] <String[]>]
    [[-InputObject] <Object[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Set-DbaSpConfigure -SqlInstance localhost -Name ScanForStartupProcedures -Value 1

Adjusts the Scan for startup stored procedures configuration value to 1 and notifies the user that this requires a SQL restart to take effect

Example: 2
PS C:\> Get-DbaSpConfigure -SqlInstance sql2017, sql2014 -Name XPCmdShellEnabled, IsSqlClrEnabled | Set-DbaSpConfigure -Value $false

Sets the values for XPCmdShellEnabled and IsSqlClrEnabled on sql2017 and sql2014 to False

Example: 3
PS C:\> Set-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled -Value 1

Adjusts the xp_cmdshell configuration value to 1.

Example: 4
PS C:\> Set-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled -Value 1 -WhatIf

Returns information on the action that would be performed. No actual change will be made.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-Value

The new value for the configuration

Alias NewValue,NewConfig
Required False
Pipeline false
Default Value 0
-Name

The name of the configuration to be set -- Configs is auto-populated for tabbing convenience.

Alias Config,ConfigName
Required False
Pipeline false
Default Value
-InputObject

Piped objects from Get-DbaSpConfigure

Alias
Required False
Pipeline true (ByValue)
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value

 

Want to see the source code for this command? Check out Set-DbaSpConfigure on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaSpConfigure.