Disk information using powershell

One of the things DBA’s need to make sure is that database server disks do not run out of space. So, the disk free space should be monitored, however, if you manage multiple servers, it is not always convenient to log into every server. The below powershell code will return the disk information including available free space.

$ServerList ="ServerA","ServerB"
foreach ($Server in $ServerList)
{
#Eliminates System Volume and DriveType=5 (Media Drive).
get-wmiobject "win32_volume" -computerName $Server | Where-Object {$_.SystemVolume -like "False" -and $_.DriveType -ne 5}|
Select-Object SystemName,Name,Label,Capacity,FreeSpace,BlockSize|Format-Table -AutoSize
}

Advertisement

Powershell script to find SQL Cluster Nodes

Below is the powershell script to find sql cluster nodes. This is similar to the sql script here except this is powershell and there is no need to connect to database server.

Param ([String[]] $ServerInstance)
$Nodelist=Invoke-Sqlcmd -Query "IF ServerProperty('ISClustered')=1 Begin SELECT NodeName,case when serverproperty('instancename') is null then 'MSSQLSERVER' Else serverproperty('instancename') End as
InstanceName,ServerProperty('ServerName') as DatabaseServer FROM sys.dm_os_cluster_nodes End" -ServerInstance $ServerInstance
for($i=0;$i -le $Nodelist.count-1; $i++)
{
get-service -computerName $Nodelist[$i].NodeName |where-Object {$_.DisplayName -like '*'+$NodeList[$i].InstanceName+'*'}|
Select-Object @{Name="ComputerName";Expression={$Nodelist[$i].NodeName}},@{Name="DatabaseServer";Expression={$Nodelist[$i].DatabaseServer}},DisplayName,Status | FT -Auto
}


Powershell Email Notifications

Using the below powershell code, we can ping the servers and set up email notifications, if any server went down. This could scheduled using Task Scheduler and set to run as frequently as possible to get notified as early as possible, in the event of a shut down.


$ServerList = "ServerA","ServerB","ServerC","ServerD"

foreach ($Server in $ServerList)
{
if (-Not(test-Connection -ComputerName $Server -Count 2 -Quiet ))
{
Send-MailMessage -To "xyz@nodomainexists.com","abc@nodomainexists.com" -Subject "The $Server server is not responding." -Body "The $Server server is not responding. This notification is coming from Powershell Script running on MachineName." -SmtpServer yourSMTPServer -From "def@nodomainexists.com"
}
}

PowerShell – Replacing backup path in SQL Jobs

Recently, I had to move backups to a different drive ,so, I copied all the existing backups to the new location but the problem is with our SQL backup jobs. Our backups are scheduled using SQL Server Agent and the backup path is hard coded in the job step. There are 100’s of jobs across multiple servers ,so, I had to edit each one of them and replace the path with the new one. This solution would take forever and so, i thought of doing it in a better way such as using a script.

Below is the powershell code which basically loops through the backup jobs in the text file(I saved the backup job names to a text file) and scripts the backup job step to a text file(backup job has multiple steps but step 1 has backup location hard coded in it) and drops that step from the job. Now rerun the script that is saved into the file after replacing the backup location with the new one.

$Module = Get-Module | where-object {$_.Name -like ‘SQLPS’} | Select-Object Name
IF ($Module -ne “SQLPS”)
{
Import-Module SQLPS
}
$BackupJobs=Get-Content "C:\Users\SQLWhisper\Documents\BackupJobs.Txt"
Foreach ($BackupJob in $BackupJobs)
{
$a =new-object microsoft.sqlserver.management.smo.server "ServerName\InstanceName"
$b=$a.JobServer.Jobs|where-Object {$_.Name -like $BackupJob}
$c=$b.JobSteps|where-object {$_.ID -like "1"}
$C.Script() |Out-File "C:\Users\SQLWhisper\Documents\BackupJobs-Step1.txt" -append
$C.Drop()
}
Remove-Module SQLPS
}

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

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”

Script Database Objects – Powershell

Powershell script to script out database objects.

1.Create the below powershell file(.ps1). This powershell file takes servername,database name and type of object(tables/stored procedures/functions..) you want to script out as input parameters. The output will be written to a file.

param(
[string]$Server,
[string]$db,
[string]$Object
)
import-module sqlps
$a = new-object microsoft.sqlserver.management.smo.server $Server
$db1=$a.databases|where-object {$_.Name -like $db} | select-object $object
$db1.$object|where-object {$_.issystemobject -like “False”}|foreach {$_.script()} |out-file “c:\users\sqlwhisper\Desktop\Scripts.txt” -append

Syntax : ./Scripts.ps1 “servername” “databasename” “tables”
Example : ./Scripts.ps1 “anulu” “yahoo” “tables” – This will script out all the tables from yahoo db on anulu server.

Powershell Script to find database server configuration Info


Add-pssnapin SQLServerCmdletsnapin100
Add-pssnapin SQLServerprovidersnapin100
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended")

$sqlserverList = ('ServerName\Instance1','ServerName\Instance2')
foreach ($SqlServer in $SqlServerList)
{
if ([array]::indexof($SqlServerList ,$SqlServer)%2 -eq 0)
{
$C ="DarkRed"
$D = "Black"
}
ELSE
{
$C= "DarkGreen"
$D = "Yellow"
}
$s = New-Object Microsoft.SQLServer.Management.Smo.Server $SQLServer
write-host '>>>>Database Server Configuration for '$SQLServer -foregroundcolor $C -BackgroundColor $d
write-host '1.InstanceName='$s.Name
write-host '2.ServerName =' $s.ComputerNamePhysicalNetBios
write-host '3.DB Service Account='$s.ServiceAccount
write-host '4.Clustered Instance='$s.IsClustered
write-host '5.Agent Service Account='$s.Jobserver.ServiceAccount
}


Fail-safe Operator – Powershell script

Powershell Script to find whether Fail-safe operator is enabled on sql server agent service. Fail-safe operator is recommended to be enabled,so, sql agent can communicate with the fail-safe operator,if it cannot notify the operator of the alert/job.


Add-pssnapin SQLServerCmdletsnapin100
Add-pssnapin SQLServerprovidersnapin100
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended")
$sqlserverList = ('SERVERNAME\INSTANCE1','SERVERNAME\INSTANCE2')
foreach($sqlserver in $sqlserverlist)
{
$s = New-Object Microsoft.SQLServer.Management.Smo.Server $SQLServer
$s.jobserver.alertsystem| select name,failsafeoperator,notificationmethod
}