Use these instructions to automate SQL backup to a storage account with private endpoints.
WHY
Azure SQL Services work great, have high availability options, built-in backup functionality, but what if someone breaks into your Azure Tenants and deletes everything!?
There are (expensive) SQL backup solutions that use various CPU-intensive techniques to backup databases, but there are Azure native techniques as well by using Az.SQL. The downside is that it’s difficult to automate unless you leave “Azure Services” open in Network Access which is a huge security risk.
The Solution
I’ve worked on automating this with AzSqlDatabaseExport + NetworkIsolation which leverages Private Links to export the data to Storage Accounts, not requiring any public endpoints. From here you can copy or sync the data elsewhere. This script hardly uses any resources at all, as it deploys the backup actions to Azure.
I’ve created this script to run on an Azure Automation Account, depending on the number of databases and amount of data, you might run into a max runtime, in which case it might be better to use an Automation Account with an Hybrid Worker, smallest VM is ok.
TIPS / DISCLAMER
Pro-tip #1: Use Azure Lighthouse to Sync the storage account to another tenant resulting in an “Off-Site” backup.
Pro-tip #2: Configure Life-Cycle management on the storage account to clean up old backup files.
Pro-tip #3: Configure Sentinel to monitor for errors and trigger Logic Apps on alerts.
I don not know your environment, please use common sense in applying this script, run it in an test environment first. Somehow, it made sense to add an disclaimer:
“[The author] assumes no responsibility or liability for any errors or omissions in the content of this site. The information contained in this site is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness or timeliness…”
The Code
This code requires PowerShell 7.1!
You can find the code on my Github has well: https://github.com/JeroenP87/AzureSQLBackup
#AzureSQLBackup
#2023.03.26 JP
#potsolutions.nl
#This code requires PowerShell 7.1!
Update-AzConfig -DisplayBreakingChangeWarning $false
try
{
"Logging in to Azure..."
Connect-AzAccount -Identity
}
catch {
Write-Error -Message $_.Exception
throw $_.Exception
}
#Azure Variables
$subscriptionid = ""
#SQL Variables
$sqlresourcegroup = ""
$sqlelasticpool = ""
$sqlserver = ""
$sqluser = ""
$secure = ConvertTo-SecureString "" -AsPlainText -Force #sqladminpass
#Storage Variables
$storageaccount = ""
$storageresourcegroup = ""
$destcontainer = ""
$deststoragekey = ""
#Local stuff
$tempdir = "C:\programdata\AzureSQLBackup"
if (test-path $tempdir) {
Remove-Item $tempdir -Recurse -Force
}
mkdir $tempdir
Start-Transcript -Path $tempdir\sqlbackup.txt
Select-AzSubscription $subscriptionid
#Starting the backup!
$databases = Get-AzSqlDatabase -ResourceGroupName $sqlresourcegroup -ServerName $sqlserver
$date = (Get-Date).ToString('yyyy-MM-dd-HH:MM')
$context = (Get-AzStorageAccount -ResourceGroupName $storageresourcegroup | Where-Object StorageAccountName -EQ $storageaccount).Context
$databases | foreach-Object -parallel {
$db = $_
if ($_.DatabaseName.Equals("master")) { continue }
if ($_.DatabaseName.Contains("COPY")) { continue }
function ApprovePendingEndpoints {
$storageeps = Get-AzPrivateEndpointConnection -privatelinkresourceid "subscriptions/$USING:subscriptionid/resourceGroups/$USING:storageresourcegroup/providers/Microsoft.Storage/storageAccounts/$USING:storageaccount" -erroraction silentlycontinue
if($storageeps) {
foreach ($storageep in $storageeps) {
if ($storageep.PrivateLinkServiceConnectionState.Status -eq "Pending") {
if (Approve-AzPrivateEndpointConnection -ResourceId $storageep.Id -erroraction silentlycontinue) {
log "storage account Private Link Approved"
}
}
}
}
$sqleps = Get-AzPrivateEndpointConnection -privatelinkresourceid "subscriptions/$USING:subscriptionid/resourceGroups/$USING:sqlresourcegroup/providers/Microsoft.Sql/servers/$USING:sqlserver" -erroraction silentlycontinue
if ($sqleps) {
foreach ($sqlep in $sqleps) {
if ($sqlep.PrivateLinkServiceConnectionState.Status -eq "Pending") {
if (Approve-AzPrivateEndpointConnection -ResourceId $sqlep.Id -erroraction silentlycontinue) {
log "SQL Private Link Approved"
}
}
}
}
}
function log {
param (
$log,
$sev = "info"
)
$message = "$((Get-Date).ToString('yyyy-MM-dd-HH:mm:ss')); $($_.DatabaseName); $($log)"
write-output $message
if ($sev.ToLower() -contains "error") {
Throw $message
}
}
start-sleep -Seconds (100..900 | get-random)
if (test-path "$USING:tempdir\$($_.DatabaseName).job") {
log "Skipping due to dual exectution.."
"$USING:tempdir\$($_.DatabaseName).job"
continue
}
Write-Output "ok" | Out-File -FilePath "$tempdir\$($_.DatabaseName).job"
try {
log "Starting Backup..."
foreach ($database in $USING:databases) {
if ($database.DatabaseName.Equals("$($db.DatabaseName)COPY")) {
log "Deleted existing COPY Database, indicating previous backup didn't run correctly."
Remove-AzSqlDatabase -DatabaseName "$($db.DatabaseName)COPY" -ServerName $db.ServerName -ResourceGroupName $db.ResourceGroupName -Force
Start-Sleep -Seconds 600
}
}
if (!(New-AzSqlDatabaseCopy -CopyDatabaseName "$($_.DatabaseName)COPY" -CopyResourceGroupName $_.ResourceGroupName -CopyServerName $_.ServerName -DatabaseName $_.DatabaseName -ResourceGroupName $_.ResourceGroupName -ServerName $_.ServerName -ElasticPoolName $USING:sqlelasticpool)) {
log "create COPY DB Error" -sev "error"
}
$op = New-AzSqlDatabaseExport -ResourceGroupName $_.ResourceGroupName `
-DatabaseName "$($_.DatabaseName)COPY" `
-ServerName $_.ServerName `
-StorageKeyType "StorageAccessKey" `
-StorageKey $USING:deststoragekey `
-StorageUri "https://$USING:storageaccount.blob.core.windows.net/$USING:destcontainer/$($_.ServerName)/$USING:date/$($_.DatabaseName).bacpac" `
-AdministratorLogin $USING:sqluser `
-AdministratorLoginPassword $USING:secure `
-UseNetworkIsolation $true `
-StorageAccountResourceIdForPrivateLink "subscriptions/$USING:subscriptionid/resourceGroups/$USING:storageresourcegroup/providers/Microsoft.Storage/storageAccounts/$USING:storageaccount" `
-SqlServerResourceIdForPrivateLink "subscriptions/$USING:subscriptionid/resourceGroups/$USING:sqlresourcegroup/providers/Microsoft.Sql/servers/$USING:sqlserver"
log "ExportID $($op.OperationStatusLink)"
$loop = 1
for ($i = 0; $loop -eq 1; $i++) {
ApprovePendingEndpoints
$importexportstatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $op.OperationStatusLink -erroraction silentlycontinue
if ($i -eq 60) {
log $importexportstatus
log $importexportstatus.Status
log $importexportstatus.StatusMessage
log $importexportstatus.ErrorMessage
log $importexportstatus.PrivateEndpointRequestStatus[0]
log $importexportstatus.PrivateEndpointRequestStatus[1]
log "Backup timed-out" -sev "error"
}
if ($importexportstatus.GetType().Name.Equals("String")) {
if ($importexportstatus.Contains("failed")) {
log $importexportstatus -sev "error"
}
}
if (!($importexportstatus)) {
Start-Sleep -Seconds 300
continue
}
if ($importexportstatus.Status -eq "Failed") {
log $importexportstatus.Status
log "Backup failed $($op.OperationStatusLink)" -sev "error"
}
if (!($importexportstatus.StatusMessage.Contains("Progress = 0 %"))) {
log "$($importexportstatus.StatusMessage)"
}
if ($importexportstatus.Status -eq "Succeeded") {
$loop = 0
} else {
Start-Sleep -Seconds 300
}
}
log "Copy is done"
start-sleep -Seconds 60
for ($i = 0; !(Get-AzStorageBlob -Blob "$($_.ServerName)/$USING:date/$($_.DatabaseName).bacpac" -Container $USING:destcontainer -Context $USING:context -erroraction silentlycontinue); $i++) {
Start-Sleep -Seconds 120
if ($i -eq 15) {
$importexportstatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $op.OperationStatusLink -erroraction silentlycontinue
log $importexportstatus
log $importexportstatus.Status
log $importexportstatus.StatusMessage
log $importexportstatus.ErrorMessage
log $importexportstatus.PrivateEndpointRequestStatus[0]
log $importexportstatus.PrivateEndpointRequestStatus[1]
log "No bacpac found after 30min" -sev "error"
}
}
log "pacpac exists"
for ($i = 0; (Get-AzStorageBlobCopyState -Blob "$($_.ServerName)/$USING:date/$($_.DatabaseName).bacpac" -Container $USING:destcontainer -Context $USING:context -erroraction silentlycontinue); $i++) {
Start-Sleep -Seconds 120
if ($i -eq 200) {
log "Copy state not ok" -sev "error"
}
}
Remove-AzSqlDatabase -DatabaseName "$($_.DatabaseName)COPY" -ServerName $_.ServerName -ResourceGroupName $_.ResourceGroupName -Force | out-null
log "COPY DB Removed"
}
catch {
Write-Error "$((Get-Date).ToString('yyyy-MM-dd-HH:mm:ss')); $($db.DatabaseName); Error backing up DB"
Write-Error -Message $_.Exception
Remove-AzSqlDatabase -DatabaseName "$($db.DatabaseName)COPY" -ServerName $db.ServerName -ResourceGroupName $db.ResourceGroupName -Force | out-null
}
} -ThrottleLimit 5
write-output "$((Get-Date).ToString('yyyy-MM-dd-HH:mm:ss'));; JOB FINISHED"
Stop-Transcript
No responses yet