10 Jul

Import bacpac from Azure Storage

Microsoft Azure has the ability to automatically backup/export your database to a certain storage account. This can be done from a maximum frequency of one time a day and a retention of maximum 90 days.
Now what if something went horribly wrong and you want to import that on specific bacpac back in your Azure SQL database?


The following PowerShell script does just that.
You will need to edit the variables though, so they will fit to your settings.

Write-Output "$(Get-Date -f $timeStampFormat) - Running"

# The variables you will need to edit to your settings
$servername = "databaseserver"
$login = "databaselogin"
$password = "databasepassword"
$databasename = "databasename"
$bacpacname = "bacpacname"
# The name of the subscription in the publishsettingsfile
$subscription = "subscription"
$storagename = "storagename"
# This key can be when you press the "manage access keys" button in the portal
$storagekey = "storagekey"
# The name of the container inside your storage which contains the .bacpac file
$containername = "containername"
$publishsettingslocation = "publishsettingslocation"

# Import the needed modules and items.
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\*.psd1"
Import-AzurePublishSettingsFile $publishsettingslocation

# Get the correct credentials and contexts
Select-AzureSubscription -SubscriptionName $subscription
$servercredential = new-object System.Management.Automation.PSCredential($login, ($password  | ConvertTo-SecureString -asPlainText -Force))
$server = Get-AzureSqlDatabaseServer -servername $servername 
$ctx = $server | New-AzureSqlDatabaseServerContext -Credential $serverCredential
$StorageCtx = New-AzureStorageContext -StorageAccountName $storagename -StorageAccountKey $storagekey
$Container = Get-AzureStorageContainer -Name $containername -Context $StorageCtx

# Restore the database to your Azure SQL.
Write-Output "$(Get-Date -f $timeStampFormat) - Restore databases"
$import = Start-AzureSqlDatabaseImport -SqlConnectionContext $ctx -StorageContainer $Container -DatabaseName $databasename -BlobName $bacpacname

# Check the status of the import
	$importStatus = Get-AzureSqlDatabaseImportExportStatus -Request $import

    # Check if import failed
	if($importStatus.Status -eq "Failed")
		Write-Output -Message $importStatus.ErrorMessage
		$importDone = 1;

    # Check if import is completed
	if($importStatus.Status -eq "Completed")
		Write-Output "$(Get-Date -f $timeStampFormat) - Restore database complete"
		$importDone = 1;

    # If not failed or completed, return the status of the current import
	if(($importStatus.Status -ne "Completed" -or $importStatus.Status -ne "Failed") -and $importDone -ne 1)
		Write-Output "$(Get-Date -f $timeStampFormat) - Import status: $($importStatus.Status)"

    # Added a sleep so that the Write-Output doesn't get flooded
	Start-Sleep -s 10
}While($importDone -ne 1)

Leave a Reply

Your email address will not be published. Required fields are marked *