Powershell is a very powerful tool to administer/manage multiple servers and services. I am trying to learn powershell and I will try to blog about as I learn more.
Starting/Stopping service on local/remote computer
–>Starting service on computer
1.Start-Service |where-object {$_.Name -like "MSSQLSERVER"}
–>Stopping Services on remote computer
2.Stop-Service -InputObject (get-service -Computername REMOTECOMPUTERNAME | where-object {$_.Name -like "MSSQLServer"}) -Force
Force is required to stop service when the service has dependent services. It stops all services. In this example, it stops both the SQL Database engine and sql agent service.
3.Measure-command is used to find which powershell query is efficient.
4. Below powershell script can be used to find the failed sql server agent jobs.
Add-pssnapin SQLServerCmdletsnapin100
Add-pssnapin SQLServerprovidersnapin100
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended")
$sqlserverList = ('SERVERNAME\INSTANCENAME','SERVERNAME\INSTANCE2')
foreach($sqlserver in $sqlserverlist)
{
$s = New-Object Microsoft.SQLServer.Management.Smo.Server $SQLServer
$s.JobServer.Jobs|Select Name,OriginatingServer,Isenabled,LastRunOutCome | where-object {$_.IsEnabled -eq 'True' -and $_.LastRunOutcome -eq 'Failed'}
}
Microsoft has a powershell webcast http://technet.microsoft.com/en-us/scriptcenter/dd901154.aspx
SQL Server Repository 