Managing SQL Server Services with WMI Provider

The WMI Provider for Configuration Management provides access to WMI objects in the root\Microsoft\SqlServer\ComputerManagement namespace for SQL Server 2005,and the root\Microsoft\SqlServer\ComputerManagement10 namespace for SQL Server 2008.

To list all the classes under the namespace:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 –list | Where-Object {-not ($_.Name -like '___*')}       ////////2008


To view a list of available services of SqlService class on your local computer:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State,ProcessId | Format-Table -wrap


To see all the methods that you can apply to the service instances:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService | Get-Member -MemberType method


To change the SQL Server service account from the local system account to a domain account, PowerDomain\SqlService, and then restart the service for the change to take effect:

$strUserName = "PowerDomain\SqlService"
# Password for the PowerDomain\SqlService account
$strPassword= "P@ssw0rd"
$sqlservice = Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName=’MSSQLSERVER’"
$sqlservice.SetServiceAccount($strUserName, $strPassword)

To change a named instance called CH0DE1 on a remote computer DEMOPC. You can change the first line of the preceding script as follows:

$sqlservice = Get-WmiObject –computerName DEMOPC –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName=’MSSQL`$CH0DE1’"


To change the start mode from manually to automatically,you can run the AutostartSQLServerAgent.ps1 script shown here:

$strComputer = "."
$sqlservice = Get-WmiObject –computerName $strComputer –namespace
root\Microsoft\SqlServer\ComputerManagement10 `
-class SqlService –filter "ServiceName=’SQLSERVERAGENT’"

Start Mode      Description
2                     Service is started automatically
3                     Service is started manually
4                     Service is disabled

个人分类: PowerShell
下一篇Managing Client Network Protocols with WMI Provider
想对作者说点什么? 我来说一句