How to Examine the SQL Server Properties with Powershell?

#Step 1 - go to the right location to see the available properties

PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir

PS SQLSERVER:\SQL\XU-ACER-PC> $a

<#

Instance Name

-------------

DEFAULT

I2

#>

PS SQLSERVER:\SQL\XU-ACER-PC> $a | gm

<#

   TypeName: Microsoft.SqlServer.Management.Smo.Server

Name                               MemberType   Definition

----                               ----------   ----------

PropertyChanged                    Event        System.ComponentModel.PropertyChangedEventHandler PropertyChanged(System.Object, System.ComponentModel.PropertyCh...

PropertyMetadataChanged            Event        System.EventHandler`1[Microsoft.SqlServer.Management.Sdk.Sfc.SfcPropertyMetadataChangedEventArgs] PropertyMetadat...

Alter                              Method       void Alter(), void Alter(bool overrideValueChecking), void IAlterable.Alter()

.........................................

Audits                             Property     Microsoft.SqlServer.Management.Smo.AuditCollection Audits {get;}

AvailabilityGroups                 Property     Microsoft.SqlServer.Management.Smo.AvailabilityGroupCollection AvailabilityGroups {get;}

BackupDevices                      Property     Microsoft.SqlServer.Management.Smo.BackupDeviceCollection BackupDevices {get;}

BackupDirectory                    Property     string BackupDirectory {get;set;}

BrowserServiceAccount              Property     string BrowserServiceAccount {get;}

BrowserStartMode                   Property     Microsoft.SqlServer.Management.Smo.ServiceStartMode BrowserStartMode {get;}

BuildClrVersion                    Property     version BuildClrVersion {get;}

BuildClrVersionString              Property     string BuildClrVersionString {get;}

BuildNumber                        Property     int BuildNumber {get;}

..............................

#>

# Step 2 - Let's say we are interested in Writable and Dirty. Writable - you have both 'get' and 'set' methods for the property. Dirty - the value has been changed in the buffer, but not on the server yet.

PS SQLSERVER:\SQL\XU-ACER-PC> $a.properties | ft name, writable,dirty -auto

# Step 3 - Witness how alter() affect Dirty

PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir | ? {$_.INSTANCEName -eq "" }

PS SQLSERVER:\SQL\XU-ACER-PC> $a.BackupDirectory="C:\users\xu-acer-pc\documents"

PS SQLSERVER:\SQL\XU-ACER-PC> $a.properties | ft name, writable,dirty -auto

<#

Name                        Writable Dirty

----                        -------- -----

AuditLevel                      True False

BackupDirectory                 True  True

BuildNumber                    False False

DefaultFile                     True False

DefaultLog                      True False

ErrorLogPath                   False False

.............

#>

PS SQLSERVER:\SQL\XU-ACER-PC> $a.Alter()

PS SQLSERVER:\SQL\XU-ACER-PC> $a.properties | ft name, writable,dirty -auto

<#

Name                        Writable Dirty

----                        -------- -----

AuditLevel                      True False

BackupDirectory                 True False

#>

# Step 3 - look at another one: SQL Server Job Properties

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> dir

<#

Name

----

Cycle Error Log

syspolicy_purge_history

#>

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a=dir

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a.properties | ft name, writable, dirty -auto

<#

Name                   Writable Dirty

----                   -------- -----

Category                   True False

CategoryID                 True False

CategoryType               True False

CurrentRunRetryAttempt    False False

CurrentRunStatus          False False

CurrentRunStep            False False

DateCreated               False False

DateLastModified          False False

DeleteLevel                True False

Description                True False

EmailLevel                 True False

EventLogLevel              True False

HasSchedule               False False

#>

#If enounting an error because of multiple instances of jobs, try below

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a | % {$_.properties | ft name, writable, dirty -auto}

# All job properties are the same, you need only one.

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a |Select -first 1 | % {$_.properties | ft name, writable, dirty -auto}