PowerShell – Script to get SQL Server Instance Info

PowerShell script to find SQL Server Instance Information
Param ([String[]] $SQLServerList)
$Module = Get-Module | where-object {$_.Name -like ‘SQLPS’} | Select-Object Name
IF ($Module -ne “SQLPS”)
{
Import-Module SQLPS
}
foreach ($SQLServer in $SQLServerList)
{
if ([array]::indexof($SqlServerList ,$SqlServer)%2 -eq 0)
{
$C =”DarkRed”
$D = “Black”
}
ELSE
{
$C= “DarkGreen”
$D = “Yellow”
}
$a=new-object Microsoft.SQLServer.Management.Smo.Server $SQLServer
Write-Host -ForeGroundColor $C -BackGroundColor $D $SQLServer ‘Server Information’
$a.Information|Select-object Parent,Version,IsSingleUser,Processors,ComputerNamePhysicalNetBios,Product,ProductLevel,Edition,PhysicalMemory,MasterDBLogPath,MasterDBPath,RootDirectory,ErrorLogPath
}
Remove-Module SQLPS

Save the script as .ps1 file(PSSQLServerInfop.ps1)
1.To run the script, set the location to file where the file exists
Set-location c:\users\sqlwhisper\Desktop
2../PSSqlServerInfo.ps1 "ServerName\InstanceName"
You can also pass multiple servernames
./PS1SqlServerInfo.ps1 -SQLServerList ServerName1\InstanceName1,ServerName2\InstanceName2

Advertisement

PowerShell – Script to Drop SQL Jobs

Powershell script to drop SQL Agent Jobs :

Param([String] $ServerName,
[String] $JobName)

$Module = Get-Module |where-object {$_.Name -like "SQLPS"} | Select-object Name
If ($Module -ne "SQLPS")
{
Import-Module SQLPS
}
$a=new-object Microsoft.SQLServer.Management.Smo.Server $ServerName
$b=$a.Jobserver.Jobs|where-object {$_.Name -like $JobName}
$b.drop()

Remove-Module SQLPS

Save the script as “PSDropSQLJob.ps1” and to run the script,
1.First the set the location to where the file exists:
set-location c:\users\sqlwhisper\desktop
2.To execute the script,
.\PSDropSQLJob.ps1 “ServerName\InstanceName” “NameOfJob”

PowerShell – script to monitor Job Activity on the Server.

Powershell script to monitor current activity on SQL Server Agent jobs. You can use the below script and find the activity of a specific job or all jobs. The output will be similar to job activity monitor in SQL Server Agent.
Script :
param(
[string]$SQLServer,
[string]$JobName)
$Module=get-module | where-object {$_.Name -like 'SQLPS'}
If ($Module -ne 'SQLPS')
{
import-module SQLPS
}
$a = new-object microsoft.sqlserver.management.smo.server $SqlServer
IF ($JobName -eq 'ALL')
{
$a.Jobserver.Jobs | select-object OriginatingServer,Name,
LastRunDate,LastRunOutCome,NextRunDate,CurrentRunStatus,
CurrentRunStep,EmailLevel,OperatorToEmail | Sort-Object LastRunOutCome,Name |FT -Auto
}
Else
{
$a.Jobserver.Jobs | where-object {$_.Name -like $JobName }| select-object OriginatingServer,Name,LastRunDate,LastRunOutCome,NextRunDate,
CurrentRunStatus,CurrentRunStep,EmailLevel,OperatorToEmail |FT -Auto
}

Remove-module sqlps

After creating the above script(save the script as “PSJobActivity.ps1”), you can run the script as
1. Before executing the script,set the path to where the script file exists.
Set-location C:users\sqlwhisperer\Desktop
2. To Execute the script
— retrieves job activity of a specific job on the server.
.\PSJobActivity.ps1 “ServerName\InstanceName” “NameOfYourJob”
–retrieves job activity of all the jobs on the server.
.\PSJobActivity.ps1 “ServerName\InstanceName” “ALL”