There are 2 ways of running PowerShell scripts in SQL Server Agent job. It can be run in,
1) PowerShell job step
2) Command prompt (CmdExec) job step
If the Windows PowerShell scripts is being run as PowerShell job step, SQL Server Agent job step run sqlps utility to launches PowerShell and load SQL Server PowerShell module (also called sqlps). The gotcha is that it launches its own PowerShell version regardless of the latest version of PowerShell installed on the server.
On SQL Server 2012, sqlps utility launches PowerShell 2.0
On SQL Server 2008/2008R2, sqlps utility launches PowerShell 1.0
If you have downloaded and installed the latest PowerShell 3.0 or currently on Windows Server 2012 or Windows 8 (pre-installed with PowerShell 3.0), you are likely to have developed your scripts in PowerShell 3.0. There are difference between PowerShell 2.0 and PowerShell 3.0, and your script written and tested in PowerShell 3.0 may not work in PowerShell 2.0 environment. (well, that what happen to me)
If you have installed PowerShell 3.0, there is no easy way to switch Windows PowerShell Integrated Scripting Environment (PowerShell ISE) to previous version. However, you could easily change version in Windows PowerShell (powershell.exe) with -version command,
powershell -version 2At least you can still verify the newly developed script this way if it works on older version. Of course you can always uninstall the latest version and install the PowerShell ISE 2.0 for your development and test.
However, in certain circumstances that you really need to use PowerShell 3.0, you could choose the second way of running PowerShell script, through CmdExec job step. SQL Server Agent uses command prompt to run PowerShell (the installed version, e.g. PowerShell 3.0) and executes the PowerShell scripts.
A quick verification on SQL Agent job step running PowerShell version,
- Create a SQL Agent job
- Create first job step, with step type change to PowerShell
- Insert the command
$psversiontable.psversion to retrieve the executing powershell version
- On advanced tab, check 'Include step output in history' to return version result in the job history for review.
- Create second job step, with step type change to Operating System (CmdExec)
- Insert the command
"C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe" "$psversiontable.psversion" to retrieve the executing powershell version. On advanced tab, check 'Include step output in history' to return version result in the job history for review.
- Save the job
- Run the job
In my environment with Windows Server 2012 and SQL Server 2012 installed, the job history result shows version 2 is returned on PowerShell step and version 3 is returned in CmdExec step.
Refer to this blog post, PowerShell and Performance Monitor (Perfmon) Counter for PowerShell script to extract performance counter and import into database for analysis.
A nice quick way to run modern Powershell with old SQL PowerShell tool: Declare a string full of modern PS script and then pass it into the server's modern "powershell".
ReplyDeleteExample:
$psCmd = '
$TRNRetentionDate = (Get-Date).AddDays(-30);
$BAKRetentionDate = (Get-Date).AddDays(-30);
Get-ChildItem -Recurse -Path "\\MyUNCPath\WontWorkInSQLPS\SQLBackups" |
Where {$_.LastWriteTime -LT $TRNRetentionDate -AND $_.PSIsContainer -Eq $False -AND $_.Extension -IN @(".bak",".trn")} |
%{
if($_.Extension -EQ ".bak" -AND $_.LastWriteTime -LT $BAKRetentionDate){
$_ | Remove-Item
}
if($_.Extension -EQ ".trn" -AND $_.LastWriteTime -LT $TRNRetentionDate){
$_ | Remove-Item
}
}
';
echo $psCmd | powershell