I have a Azure Automation PowerShell script the gets scheduled to run every night. For some reason every once and while the workflow will run and then just start running all the commands from the beginning again in the same instance of the running workflow. I am not looping the whole workflow or anything so i do not know how or why this is happening. Any thoughts?
Here is the code:
workflow Start-SQLDatabaseBackup
{
param(
[parameter(Mandatory=$true)]
[string] $credName = 'automation',
[parameter(Mandatory=$true)]
[string] $SubscriptionName,
[parameter(Mandatory=$true)]
[string] $SQLServerName,
[parameter(Mandatory=$true)]
[string] $DatabaseName,
[parameter(Mandatory=$true)]
[string] $StorageAccountName,
[parameter(Mandatory=$true)]
[string] $ContainerName ='backup',
[parameter(Mandatory=$false)]
[string] $time
)
inlinescript {
Write-Output ("Starting Database Backup for " + $Using:DatabaseName)
$Credential = Get-AutomationPSCredential -Name $Using:credName
if($Credential)
{
Write-Output ("Found Automation Credential Asset named " + $Using:credName)
}
else
{
throw ("Could not find an Automation Credential Asset named" + $Using:credName + ". Make sure you have created one in this Automation Account." )
}
$SQLCredential = Get-AutomationPSCredential -Name '****'
if($SQLCredential)
{
Write-Output ("Found SQL Credential Asset named " + $Using:SQLcredName)
}
else
{
throw ("Could not find an SQL Credential Asset named " + $Using:SQLcredName + ". Make sure you have created one in this Automation Account.")
}
Add-AzureAccount -Credential $Credential > $null
Select-AzureSubscription -SubscriptionName $Using:SubscriptionName
if (!(Test-AzureName -Storage $Using:StorageAccountName))
{
Write-Output ("Creating Storage Account " + $Using:StorageAccountName)
New-AzureStorageAccount -StorageAccountName $Using:StorageAccountName -Location $Using:Location
}
Set-AzureSubscription -SubscriptionName $Using:SubscriptionName -CurrentStorageAccountName $Using:StorageAccountName
$SqlContext = New-AzureSqlDatabaseServerContext -ServerName $Using:SQLServerName -Credential $SQLCredential
if($SqlContext)
{
Write-Output ("Created SQL Context for " + $Using:SQLServerName)
}
else
{
throw ("Could not Create SQL Context for " + $Using:SQLServerName + ". Make sure SQL Credential Asset named" + $Using:SQLcredName + " has access to the server" )
}
$time = $Using:time
if ([string]::IsNullOrEmpty($time) )
{
$start = [System.DateTime]::UtcNow
$time = $start.ToString("yyyyMMdd_hh-mm-ss-tt")
}
$fileName = ( "{2}/{0}_{1}.bacpac" -f $Using:DatabaseName, $time, $time )
$Container = Get-AzureStorageContainer -Name $Using:ContainerName
if($Container)
{
Write-Output ("Retrieved Azure Storage Container - " + $Using:ContainerName)
}
else
{
throw ("Could not Retrieve Azure Storage Container " + $Using:ContainerName + ". Make sure the Storage Container exists. " )
}
Write-Output ("Starting Copy of " + $Using:DatabaseName)
$dbCopyName = ($Using:DatabaseName +"_copy")
$dbCopy = Start-AzureSqlDatabaseCopy -ServerName $Using:SQLServerName -DatabaseName $Using:DatabaseName -PartnerDatabase $dbCopyName
$doLoop = 1
while ($doLoop -eq 1)
{
$copyStatus = Get-AzureSqlDatabaseCopy -ServerName $Using:SQLServerName -DatabaseCopy $dbCopy -ErrorAction SilentlyContinue
if ($copyStatus -ne $null)
{
Write-Output $copyStatus.PercentComplete
Start-Sleep -s 10
}
else
{
$doLoop=0
Start-Sleep -s 10
}
}
Get-AzureSqlDatabase -ConnectionContext $SqlContext -DatabaseName $dbCopyName
Write-Output ("Starting Export of " + $dbCopyName )
$exportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $SqlContext -StorageContainer $Container -DatabaseName $dbCopyName -BlobName $fileName
if ($exportRequest)
{
$doLoop = 1
while ($doLoop -eq 1)
{
$exportStatus = Get-AzureSqlDatabaseImportExportStatus -Request $exportRequest
if ($exportStatus.Status -eq "Completed")
{
$doLoop = 0
}
elseif ($exportStatus.Status -eq "Failed")
{
$doLoop = 0
throw ("Exporting database " + $dbCopyName + " failed")
}
else
{
Write-Output $exportStatus.Status
Start-Sleep -s 10
}
}
Write-Output ("Backup " + $fileName + " Created" )
}
else
{
throw ("Error Creating Export Request for " + $dbCopyName)
}
Write-Output ("Deleting " + $dbCopyName)
Remove-AzureSqlDatabase -ConnectionContext $SqlContext -DatabaseName $dbCopyName -Force
}
}
And here is the output. You can see it starts running the commands from the top of the script again after it finished.
Starting Database Backup for *****
Found Automation Credential Asset named ****
Found SQL Credential Asset named
Created SQL Context for *****
Retrieved Azure Storage Container - backup
Starting Copy of ******
0
0
0
0
0
0
0
100
Starting Export of ******
Pending
Running, Progress = 0%
**Starting Database Backup for ******** < -- Starts running from the top of script again?
Found Automation Credential Asset named ****
Found SQL Credential Asset named
Created SQL Context for *******
Retrieved Azure Storage Container - backup
Starting Copy of *****
Starting Export of *****
