Setup tips for SQL DB auto export PowerShell scripts
SQL Database offered to backup SQL Database instances with their build-in features, but it was expired now. You can choose below options.
- Long term retention for SQL DB https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-retention
- Automate export PowerShell script with Azure Automation https://github.com/Microsoft/sql-server-samples/tree/master/samples/manage/azure-automation-automated-export
In this post, I will introduce setup tips for the scripts. Please read README of "Automate export PowerShell script with Azure Automation" to setup this script at first.
Add SQL DB instances into single script
You can add other databases to add them into “$databaseServerPairs” in below code.
And please use other credentials if you use other SQL Database servers.
Export error when SQL DB instances so large
Please read this section when you get below error.
The error message is caused by below line.
- https://github.com/Microsoft/sql-server-samples/blob/master/samples/manage/azure-automation-automated-export/AutoExport.ps1#L115
The error is caused by below, so it seems to take too long time to copy DB data.
if((-not $? -and $global:retryLimit -ile $dbObj.RetryCount) -or ($currentTime - $dbObj.OperationStartTime).TotalMinutes -gt $global:waitInMinutes)
Please change variable “$waitInMinutes = 30;” from 30 minutes to long time.
In order to execute the runbook do I need to have the automation account to have the ability to “Run As account”?
“Azure Run As account” is needed, because we can't execute Runbook scripts without this. It needs to enable Azure Active Directory to register applications.
https://docs.microsoft.com/en-us/azure/automation/automation-create-aduser-account#create-an-automation-account-in-the-azure-portal
" 429 Too many requests" error in Runbook Job log when exporting large SQL Database instances
You will get below error when you execute long jobs.
Get-AzureSqlDatabaseImportExportStatus : A task was canceled.
At line:181 char:11
+ ... $check = Get-AzureSqlDatabaseImportExportStatus -Request $dbObj.Ex ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-AzureSqlDatabaseImportExportStatus], TaskCanceledException
+ FullyQualifiedErrorId :
Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.GetAzureSqlDatabaseImportExportStatus
The error is caused by frequent requests using “Get-AzureSqlDatabaseImportExportStatus”, so it need to insert “Start-Sleep” in the script to reduce Azure Management API calls internally.
