Tuesday, June 22, 2021

Automate exporting of azure sql database as .bacpac to blog storage.

Login to Azure Portal-->Go to automation account -->Runbooks-->browse gallery -->type backup azure sql databases to blob storage and import the module.

v-pekapa_0-1622648842657.png

  1. Open the run book and click edit.
    v-pekapa_1-1622649125019.png

    v-pekapa_2-1622649175619.png

     

  2. Download the code and replace the with your details as shown below:
    v-pekapa_3-1622649209874.png
    <#
    .OUTPUTS
    	Human-readable informational and error messages produced during the job. Not intended to be consumed by another runbook.
    
    #>
    
    
        $RESOURCEGROUPNAME = '<ResourceGroupName>'
        $DatabaseServerName = '<DatabaseName>'
        $DatabaseAdminUsername = '<AdminAccountHere>'
        $DatabaseAdminPassword = '<YourPasswordHere>'
        $DatabaseNames = '<DatabaseNaem>'
        $StorageAccountName = '<StorageAccountName>'
        $BlobStorageEndpoint = 'https://<StorageAccountName>.blob.core.windows.net/'
        $StorageKey = '<StorageAccessKey>'
        $BlobContainerName = '<ContainerName>'
        $RetentionDays = '10'
    
    
    $ErrorActionPreference = 'stop'
    
    function Login() {
    	$connectionName = "AzureRunAsConnection"
    	try
    	{
    		$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName         
    
    		Write-Verbose "Logging in to Azure..." -Verbose
    
    		Add-AzureRmAccount `
    			-ServicePrincipal `
    			-TenantId $servicePrincipalConnection.TenantId `
    			-ApplicationId $servicePrincipalConnection.ApplicationId `
    			-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint | Out-Null
    	}
    	catch {
    		if (!$servicePrincipalConnection)
    		{
    			$ErrorMessage = "Connection $connectionName not found."
    			throw $ErrorMessage
    		} else{
    			Write-Error -Message $_.Exception
    			throw $_.Exception
    		}
    	}
    }
    
    function Create-Blob-Container([string]$blobContainerName, $storageContext) {
    	Write-Verbose "Checking if blob container '$blobContainerName' already exists" -Verbose
    	if (Get-AzureStorageContainer -ErrorAction "Stop" -Context $storageContext | Where-Object { $_.Name -eq $blobContainerName }) {
    		Write-Verbose "Container '$blobContainerName' already exists" -Verbose
    	} else {
    		New-AzureStorageContainer -ErrorAction "Stop" -Name $blobContainerName -Permission Off -Context $storageContext
    		Write-Verbose "Container '$blobContainerName' created" -Verbose
    	}
    }
    
    function Export-To-Blob-Storage([string] $RESOURCEGROUPNAME, [string]$databaseServerName, [string]$databaseAdminUsername, [string]$databaseAdminPassword, [string[]]$databaseNames, [string]$storageKey, [string]$blobStorageEndpoint, [string]$blobContainerName) {
    	Write-Verbose "Starting database export to databases '$databaseNames'" -Verbose
    	$securePassword = ConvertTo-SecureString –String $databaseAdminPassword –AsPlainText -Force 
    	$creds = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $databaseAdminUsername, $securePassword
    
    	foreach ($databaseName in $databaseNames.Split(",").Trim()) {
    		Write-Output "Creating request to backup database '$databaseName'"
    
    		$bacpacFilename = $databaseName + (Get-Date).ToString("yyyyMMddHHmm") + ".bacpac"
    		$bacpacUri = $blobStorageEndpoint + $blobContainerName + "/" + $bacpacFilename
    		$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $RESOURCEGROUPNAME –ServerName $databaseServerName `
    			–DatabaseName $databaseName –StorageKeytype "StorageAccessKey" –storageKey $storageKey -StorageUri $BacpacUri `
    			–AdministratorLogin $creds.UserName –AdministratorLoginPassword $creds.Password -ErrorAction "continue"
    		
    		# Print status of the export
    		Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink -ErrorAction "Stop"
    	}
    }
    
    function Delete-Old-Backups([int]$retentionDays, [string]$blobContainerName, $storageContext) {
    	Write-Output "Removing backups older than '$retentionDays' days from blob: '$blobContainerName'"
    	$isOldDate = [DateTime]::UtcNow.AddDays(-$retentionDays)
    	$blobs = Get-AzureStorageBlob -Container $blobContainerName -Context $storageContext
    	foreach ($blob in ($blobs | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate -and $_.BlobType -eq "BlockBlob" })) {
    		Write-Verbose ("Removing blob: " + $blob.Name) -Verbose
    		Remove-AzureStorageBlob -Blob $blob.Name -Container $blobContainerName -Context $storageContext
    	}
    }
    
    Write-Verbose "Starting database backup" -Verbose
    
    $StorageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageKey
    
    Login
    
    Create-Blob-Container `
    	-blobContainerName $blobContainerName `
    	-storageContext $storageContext
    	
    Export-To-Blob-Storage `
    	-resourceGroupName $RESOURCEGROUPNAME `
    	-databaseServerName $DatabaseServerName `
    	-databaseAdminUsername $DatabaseAdminUsername `
    	-databaseAdminPassword $DatabaseAdminPassword `
    	-databaseNames $DatabaseNames `
    	-storageKey $StorageKey `
    	-blobStorageEndpoint $BlobStorageEndpoint `
    	-blobContainerName $BlobContainerName
    	
    
    	
    Write-Verbose "Database backup script finished" -Verbose
    

You can get required details from azure portal as shown in below snippets:
To get the Blob container name: Go to the storage account and select the container name where you want to save the bacpac file .

v-pekapa_4-1622649277558.png

 

 

To find the storage key: Go to Access keys and copy the key as shown in below image:

v-pekapa_5-1622649352602.png

 

 

You can get the Blob Storage Endpoint from endpoint option as shown in the below image.

v-pekapa_6-1622649389938.png

 

  1. Now go to runbook and click on edit PowerShell runbook option and replace the existing script with the modified script that you have.
    v-pekapa_7-1622649423754.png

     

  2. Now to automate click on “link to schedule” and schedule the time according to your requirement in order to run the export job and click on create.
    v-pekapa_8-1622650923285.png

     

6.If you want to confirm If bacpac is completed successfully, you can go to job output and check the status of backup.

v-pekapa_9-1622651112359.png

 

You can also verify the status in SQL Server import and export history wizard as shown below:

v-pekapa_10-1622651148395.png

 

Once the backup is successful, you can find the .bacpac file in your specific storage account.

v-pekapa_11-1622651175715.png

 

Points to ponder:

  1. Make sure that you have set the option Allow all azure services to “Yes”.
  2. This functionality works only when public end point has been used and make sure deny public access should be set to “NO"
  3. Also If you encounter any issue related to module is miss then you need to go the below path to fix the issue:
    Go  the automation account-->module-->browse gallery--> Search missing module-->Import
Posted at https://sl.advdat.com/3j35xpO