Get-DbaSpConfigure

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

Synopsis

Returns all server level system configuration (sys.configuration/sp_configure) information

Description

This function returns server level system configuration (sys.configuration/sp_configure) information. The information is gathered through SMO Configuration.Properties.
The data includes the default value for each configuration, for quick identification of values that may have been changed.

Syntax

Get-DbaSpConfigure
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Name] <String[]>]
    [[-ExcludeName] <String[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaSpConfigure -SqlInstance localhost

Returns all system configuration information on the localhost.

Example: 2

PS C:\> 'localhost','localhost\namedinstance' | Get-DbaSpConfigure

Returns system configuration information on multiple instances piped into the function

Example: 3

PS C:\> Get-DbaSpConfigure -SqlInstance sql2012 -Name 'max server memory (MB)'

Returns only the system configuration for MaxServerMemory on sql2012.

Example: 4

PS C:\> Get-DbaSpConfigure -SqlInstance sql2012 -ExcludeName 'max server memory (MB)', RemoteAccess | Out-GridView

Returns system configuration information on sql2012 but excludes for max server memory (MB) and remote access. Values returned in grid view

Example: 5

PS C:\> $cred = Get-Credential SqlCredential
PS C:\> 'sql2012' | Get-DbaSpConfigure -SqlCredential $cred -Name RemoteAccess, 'max server memory (MB)' -ExcludeName 'remote access' | Out-GridView

Returns system configuration information on sql2012 using SQL Server Authentication. Only MaxServerMemory is returned as RemoteAccess was also excluded.

Required Parameters

-SqlInstance

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

Optional Parameters

-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.

-Name

Return only specific configurations. Name can be either values from (sys.configuration/sp_configure) or from SMO object

-ExcludeName

Exclude specific configurations. Name can be either values from (sys.configuration/sp_configure) or from SMO object

-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.