dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Set-DbaSpConfigure

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.

Screenshots

Examples

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

Set-DbaSpConfigure -SqlServer localhost -configs ScanForStartupProcedures -value 1

Adjusts the xp_cmdshell configuation value to 1.

Set-DbaSpConfigure -SqlServer localhost -configs XPCmdShellEnabled -value 1

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

Set-DbaSpConfigure -SqlServer localhost -configs XPCmdShellEnabled -value 1 -WhatIf

Author

This command was created by Nicholas Cain, SQL Server MCM. You can find Nic on Twitter and his blog.

Get-Help

From PowerShell, execute Get-Help Set-DbaSpConfigure -Detailed for more information on this function.

Source Code

Want to see the source code? View Set-DbaSpConfigure.ps1 on GitHub
 

Related commands