If using SQL Server 2012 and a SQL Agent job configured with Type "Powershell", you may recieve the below error when executing Azure Powerhell Cmdlets.
The term 'New-AzureStorageContext' is not recognized as the name of a cmdlet, function, script file, or operable program.
The main roadblock is SQL Server 2012 SQL Agent uses SQLPS.exe to launch PowerShell, when you are using the PowerShell SQL Job type, and SQLPS.exe uses PowerShell 2.0 – This version of Powershell cannot import the Azure cmdlets, and there isn’t a way to force SQLPS.exe to launch another newer version of Powershell (PS ver. 4.0 would be needed to import the Azure cmdlets). Starting in SQL Server 2014, it was changed where SQLPS.exe will launch PS 4.0, and SQL Server 2016 uses SQLPS.exe to run whatever the latest PS version is installed. There may be other compatibility issues even up to 2016 with regards to running PS commands directly in a SQL Agent job, but this is much improved in SQL Server 2017.
Workaround for SQL Server 2012:
Use a Batch File to run the PowerShell script. Then not using SQLPS.exe – instead, the batch file fires off PowerShell.exe for a full PS environment. This way it’ll use the latest version of PowerShell installed, complete with the Azure cmdlets.
1. Create a .bat file with the below code:
@echo off
Powershell.exe -executionpolicy remotesigned -File F:\AzureCopy\AzureDelete.ps1
2. Add your Powershell Cmdlets in the .ps1 file in Step 1:
Example: Delete SQL Server backup files (.bak, .log, .sqb)
$StorageAccount = ''
$StorageAccountKey = ''
$StorageAccountContainer = ''
$Context = New-AzureStorageContext -StorageAccountName $StorageAccount -StorageAccountKey $StorageAccountKey
$RetentionPeriodInHours = [DateTime]::UtcNow.AddHours(-336)
Get-AzureStorageBlob -Container $StorageAccountContainer -Context $Context | Where-Object {$_.LastModified.UtcDateTime -lt $RetentionPeriodInHours -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$RetentionPeriodInHours = [DateTime]::UtcNow.AddHours(-336)
Get-AzureStorageBlob -Container $StorageAccountContainer -Context $Context | Where-Object {$_.LastModified.UtcDateTime -lt $RetentionPeriodInHours -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob
$RetentionPeriodInHours = [DateTime]::UtcNow.AddHours(-336)
Get-AzureStorageBlob -Container $StorageAccountContainer -Context $Context | Where-Object {$_.LastModified.UtcDateTime -lt $RetentionPeriodInHours -and $_.BlobType -eq "BlockBlob" -and $_.Name -like "*.sqb"} |
Remove-AzureStorageBlob
3. Create a SQL Job as Type "Operating system (CmdExec)" with the below call:
F:\AzureCopy\AzureDelete.bat
Resources:
SQL PowerShell: July 2016 update
|
|
|
|
|
|