Call powershell from SQL Agent Job to refresh a clone

giumaRE · July 14, 2022 11:42AM

Hi,

For my convenience I manage the update of clones via powershell scripts launched using sql job agent.

The problem is that the job does not raise the error in case there are problems when generating the clone.

The solution that I find is to insert the code $ErrorActionPreference= "Stop",

before executing the powershell script, but it doesn't meet my needs

This is the command, with type Powershell, in my job step:

<div>$ErrorActionPreference= "Stop"</div><div>C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe E:\PS\RefreshClone.ps1 -MachineName 'sqlMT1-TEST' -ImageName '*_ArchivioAziende_*'&nbsp;</div>

But during the execution of the below script, the sql agent raises the error:

<div># First step:&nbsp;&nbsp;</div><div>try{</div><div>Get-SqlClone -Name $DatabaseName -Location $SqlServerInstance -ErrorAction Continue</div><div>}catch{</div><div>"ERROR::<< {0} >>" -f $error.Exception.Message&nbsp;</div><div>}</div>

What I need is that, if the clone exists , the clone will be deleted and if the clone doesn't exist the script should continue the execution instead of the sql agent raising an error.

Hope I was clear.
Thanks

giumaRE · July 14, 2022 3:53PM

This is the problem that I found out while I was executing the script below.

Get-SQLClone function has different behaviour in OPTION 1 and OPTION 2
OPTION 2 raise an exception,
OPTION 1 no

Connect-SqlClone -ServerUrl $ServerUrl

#list available image related to $ImageName parameter
$images = Get-SqlCloneImage -Name $ImageName

#get the last image available

$image = $images | Sort-Object -Property CreatedDate -Descending | Select-Object -First 1

# get the sql clone sql server instance

$sqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName $MachineName

########################## OPTION 1 ################

$clones = Get-SqlClone -Image $image

if (!($null -eq $clones))

{

"Will not remove image {0} because it has {1} dependent clone(s)." -f $image.Name, $clones.Count

}

else

{

"Will not remove image {0} because this is a test." -f $image.Name

}

##################################################

#####################OPTION 2 #########################

try{

$clones = Get-SqlClone -Name $DatabaseName -Location $SqlServerInstance

}

catch {

"ERROR::<< {0} >>" -f $error.Exception.Message

# Write-Host $_

Write-Output $error.Exception.Message

# exit -1;

}
##################################################

Eddie D · July 19, 2022 9:51AM

Hi,
Thank you for your forum post.

I am little confused by your post.

The purpose of the Get-SqlClone Cmdlet is to return a CloneResource that can then be used by the Remove-SqlClone cmdlet. So if the Clone does not exist, I would expect a message to be returned to indicate that it does not exist.

In the help documentation there is a worked example on refeshing clones to use an updated image, available here.

Many Thanks
Eddie

giumaRE · July 19, 2022 10:32AM

Hi Eddie,

yes, exactly..it is what I would expect too, instead I receive an error.
I Don't understand why in the first case if I don't find a clone resource the cmdlet returns null, instead in the second case if I don't find a clone resource the cmdlet returns an error.

giumaRE · July 19, 2022 10:46AM

I followed the instructions of the link you sent me and I changed the code in this way

$clones = Get-SqlClone | Where-Object { $_.Name -eq $DatabaseName -and $_.LocationId -eq $SqlServerInstance.Id }

Thank you, Eddie

Call powershell from SQL Agent Job to refresh a clone

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk