Get-DbaInstanceProperty

Synopsis

Retrieves comprehensive SQL Server instance configuration properties for auditing and comparison

Description

Retrieves all instance-level configuration properties from SQL Server’s Information, UserOptions, and Settings collections via SMO. This gives you a complete inventory of server settings like default file paths, memory configuration, security options, and user defaults in a standardized format. Essential for configuration audits, compliance reporting, environment comparisons, and troubleshooting configuration-related issues across multiple instances.

Syntax

Get-DbaInstanceProperty
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-InstanceProperty] <Object[]>]
    [[-ExcludeInstanceProperty] <Object[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaInstanceProperty -SqlInstance localhost

Returns SQL Server instance properties on the local default SQL Server instance

Example: 2

PS C:\> Get-DbaInstanceProperty -SqlInstance sql2, sql4\sqlexpress

Returns SQL Server instance properties on default instance on sql2 and sqlexpress instance on sql4

Example: 3

PS C:\> 'sql2','sql4' | Get-DbaInstanceProperty

Returns SQL Server instance properties on sql2 and sql4

Example: 4

PS C:\> Get-DbaInstanceProperty -SqlInstance sql2,sql4 -InstanceProperty DefaultFile

Returns SQL Server instance property DefaultFile on instance sql2 and sql4

Example: 5

PS C:\> Get-DbaInstanceProperty -SqlInstance sql2,sql4 -ExcludeInstanceProperty DefaultFile

Returns all SQL Server instance properties except DefaultFile on instance sql2 and sql4

Example: 6

PS C:\> $cred = Get-Credential sqladmin
PS C:\> Get-DbaInstanceProperty -SqlInstance sql2 -SqlCredential $cred

Connects using sqladmin credential and returns SQL Server instance properties from sql2

Required Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.

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.

-InstanceProperty

Specifies which SQL Server instance properties to include from Information, UserOptions, and Settings collections. Accepts wildcards and arrays.
Use this to focus on specific configuration properties like DefaultFile, MaxWorkerThreads, or LoginMode when auditing particular settings across instances.

-ExcludeInstanceProperty

Specifies which SQL Server instance properties to exclude from the results. Accepts wildcards and arrays.
Use this to filter out noisy or irrelevant properties when you need a cleaner view of configuration data for reporting or comparison.

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

Outputs

PSCustomObject

Returns one object per instance property from the Information, UserOptions, and Settings collections. The function returns properties from three separate SMO collections, outputting each property with contextual information about which collection it came from.

Default display properties (via Select-DefaultView):

• ComputerName: The name of the computer hosting the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name (computer\instance format)
• Name: The property name (e.g., DefaultFile, MaxWorkerThreads, LoginMode, TcpPort)
• Value: The current value of the configuration property (string or mixed type depending on property)
• PropertyType: The type of property collection - either “Information”, “UserOption”, or “Setting” The -InstanceProperty and -ExcludeInstanceProperty parameters filter which specific properties are returned but do not change the output structure.