PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language.

Can be run as Jobs in SQL Server.


Run Script From SQL Job

Type: Operating system (CmdExec)

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "C:\MyDirectory01\MyScript.ps1"

Remove all files greater than a certain date.

Type: PowerShell

$Path = "C:\MSSQL\DBBackups\"
$Daysback = "-7"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
Get-ChildItem $Path -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete -and $_.Extension -eq ".bak" } | Remove-Item -Recurse -ErrorAction  SilentlyContinue

PowerShell Commands

Here are 25 basic PowerShell commands:

Command nameAliasDescription
Set-Locationcd, chdir, slSets the current working location to a specified location.
Get-Contentcat, gc, typeGets the content of the item at the specified location.
Add-ContentacAdds content to the specified items, such as adding words to a file.
Set-ContentscWrites or replaces the content in an item with new content.
Copy-Itemcopy, cp, cpiCopies an item from one location to another.
Remove-Itemdel, erase, rd, ri, rm, rmdirDeletes the specified items.
Move-Itemmi, move, mvMoves an item from one location to another.
Set-ItemsiChanges the value of an item to the value specified in the command.
New-ItemniCreates a new item.
Start-JobsajbStarts a Windows PowerShell background job.
Compare-Objectcompare, difCompares two sets of objects.
Group-ObjectgroupGroups objects that contain the same value for specified properties.
Invoke-WebRequestcurl, iwr, wgetGets content from a web page on the Internet.
Measure-ObjectmeasureCalculates the numeric properties of objects, and the characters, words, and lines in string objects, such as files …
Resolve-PathrvpaResolves the wildcard characters in a path, and displays the path contents.
Resume-JobrujbRestarts a suspended job
Set-Variableset, svSets the value of a variable. Creates the variable if one with the requested name does not exist.
Show-CommandshcmCreates Windows PowerShell commands in a graphical command window.
Sort-ObjectsortSorts objects by property values.
Start-ServicesasvStarts one or more stopped services.
Start-Processsaps, startStarts one or more processes on the local computer.
Suspend-JobsujbTemporarily stops workflow jobs.
Wait-JobwjbSuppresses the command prompt until one or all of the Windows PowerShell background jobs running in the session are …
Where-Object?, whereSelects objects from a collection based on their property values.
Write-Outputecho, writeSends the specified objects to the next command in the pipeline. If the command is the last command in the pipeline,…


Last modified: January 2, 2020



Write a Reply or Comment