Changing SQL Server Advanced Properties with WMI Provider

Suppose the file system is corrupted,in which case you need to restore every database, including the master database. To restore the master database, you need to start the SQL Server in single-user mode. Insert the –m option into the existing startup options, and then restart the database.

The ChangeStartupParameters.ps1 script is shown in the following example. The script first uses the Get-WmiObject cmdlet to instantiate a SqlServiceAdvancedProperty object that corresponds to the STARTUPPARAMETERS property of the default instance. Next, it invokes the SetStringValue method to pre-append the -m option to the string of startup parameters. The ‘‘`n’’ in the script is used to print out the new line character.

# . for the local computer
# If you want to connect to a remote machine, specify the machine name here.
$strComputer='.'
# Name of the targeted service. Here the default SQL Server service is targeted.
# For a named instance INSTANCE1, use "MSSQL`$INSTANCE1"
$strServiceName = "MSSQLSERVER"
$ap=Get-WmiObject -computerName $strComputer –namespace
root\Microsoft\SqlServer\ComputerManagement10 -class SqlServiceAdvancedProperty `
-filter "ServiceName='$strServiceName' and PropertyName='STARTUPPARAMETERS'"
$ap.SetStringValue("-m " + $ap.PropertyStrValue) | Out-Null
# Confirm the "-m" option has been added.
$ap=Get-WmiObject -computerName $strComputer –namespace
root\Microsoft\SqlServer\ComputerManagement10 -class SqlServiceAdvancedProperty `
-filter "ServiceName='$strServiceName' and PropertyName='STARTUPPARAMETERS'"
Write-Host "The startup parameters have been changed to `n" $ap.PropertyStrValue

Note:The new startup option will take effect the next time SQL Server starts.

 

To look at the read-only properties of the SQL Server service:

$strComputer='.'

$strServiceName = "MSSQLSERVER"
$properties=Get-WmiObject -computerName $strComputer –namespace
root\Microsoft\SqlServer\ComputerManagement10 -class SqlServiceAdvancedProperty
-filter "ServiceName=’$strServiceName’"
$properties | Where-Object {$_.IsReadOnly} | Format-table -wrap PropertyName,
PropertyStrValue, PropertyNumValue