How can we help you today? How can we help you today?

Call powershell from SQL Agent Job to refresh a clone

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:

$ErrorActionPreference= "Stop"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe E:\PS\RefreshClone.ps1 -MachineName 'sqlMT1-TEST' -ImageName '*_ArchivioAziende_*' 
But during the execution of the below script, the sql agent raises the error: 
# First step:  try{Get-SqlClone -Name $DatabaseName -Location $SqlServerInstance -ErrorAction Continue}catch{"ERROR::<< {0} >>" -f $error.Exception.Message }
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
0

Comments

4 comments

Sort by
  • giumaRE
    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;
        
    }
    ##################################################


    giumaRE
    0
  • Eddie D
    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
    Eddie D
    0
  • giumaRE
    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
    0
  • giumaRE
    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
    giumaRE
    0

Add comment

Please sign in to leave a comment.