Press "Enter" to skip to content

How to export Sitecore SQL Azure databases to Storage Account

The script underneath can be used to export a sql azure database to a storage account.
It will create a storage account if it does not exist. Remark: script underneath is based
on exporting Sitecore master, core and web database but can be changed to your standards easily.

Note: the database names we use are based on tfs_buildnumbers that we set in a appsetting within
the azure app service. Please change the postfix according to your needs or remove the appsetting configuration
completely.

#Please adjust variables beneath according to your environment
$Environment = ‘tst’
$CustomerPrefix = ‘bst’
$ResourceGroupLocation = ‘West Europe’
$ResourceGroupPaas = ‘tst-bst-paas-rg’
$sqlusername = ‘adminUserSqlServerAzure’
$sqlServerPassword = ‘passwordAdminUser’
$ResourceGroupSqlbackup = $Environment + ‘-‘ + $CustomerPrefix + ‘-sqlbackup-‘ + ‘rg’
$AzureBlobStorageNameSqlBackup = $Environment + $CustomerPrefix + ‘sqlbackupstorage’

#Database names will be based on the app setting (key = deployment ) in the staging webapp
$mySite = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-single’
$mySitestaging = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-single-staging’
$slotstaging = ‘staging’
$webAppStaging = Get-AzureRMWebAppSlot -ResourceGroupName $ResourceGroupPaas -Name $mySite -Slot staging
$appSettingListStaging = $webAppStaging.SiteConfig.AppSettings
$definenameStaging = $appSettingListStaging | where-object {$_.name -eq “deployment”}
$valueofdeploymentsettingstaging = $definenameStaging.Value

#Define your SQL Server names in Azure
$SourceServerName = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-sql’
$SourceServerNameWeb = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-web-sql’

#Define your Sitecore database names
$SourceDatabaseNameCore = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-core-db’
$SourceDatabaseNameMaster = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-master-db’
$SourceDatabaseNameWeb = $Environment + ‘-‘ + $CustomerPrefix + ‘-paas-rg-web-db’

$ErrorActionPreference = “Stop”
$ContainerName = “sqlbackups”

#Check if resourcegroup already exists
Get-AzureRmResourceGroup -Name $ResourceGroupSqlbackup -Location $ResourceGroupLocation -ev notPresent -ea 0

if($notPresent)
{
Write-Host “Resourcegroup for SQL backups does not exist. Creating resource group, storageaccount and sqlbackups container”

#Create a resourcegroup
$ResourceGroup = @{
Name = $ResourceGroupSqlbackup;
Location = $ResourceGroupLocation;
Force = $true;
}
New-AzureRmResourceGroup @ResourceGroup;

#Create a storageaccount
$StorageAccount = @{
ResourceGroupName = $ResourceGroupSqlbackup;
Name = $AzureBlobStorageNameSqlBackup;
SkuName = ‘Standard_LRS’;
Location = $ResourceGroupLocation;
}
New-AzureRmStorageAccount @StorageAccount;

### Obtain the Storage Account authentication keys using Azure Resource Manager (ARM)
$Keys = Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupSqlbackup -Name $AzureBlobStorageNameSqlBackup;

### Use the Azure.Storage module to create a Storage Authentication Context
$StorageContext = New-AzureStorageContext -StorageAccountName $AzureBlobStorageNameSqlBackup -StorageAccountKey $Keys[0].Value;

### Create a Blob Container in the Storage Account
New-AzureStorageContainer -Context $StorageContext -Name $ContainerName -Permission Off ;

}
else
{
Write-Host “ResourceGroup $ResourceGroupSqlbackup already exists. Skipping step.”
}

### Obtain the Storage Account authentication keys using Azure Resource Manager (ARM)
$Keys = Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupSqlbackup -Name $AzureBlobStorageNameSqlBackup;

### Use the Azure.Storage module to create a Storage Authentication Context
$StorageContext = New-AzureStorageContext -StorageAccountName $AzureBlobStorageNameSqlBackup -StorageAccountKey $Keys[0].Value;

### Check if container already exists:
Get-AzureStorageContainer -Context $StorageContext -Name $ContainerName -ev notPresent -ea 0

if($notPresent)
{
### Create a Blob Container in the Storage Account
New-AzureStorageContainer -Context $StorageContext -Name $ContainerName -Permission Off ;
}
else
{
Write-Host “‘Container: ‘ $ContainerName ‘ already exists. Skipping step.'”
}

if ([string]::IsNullOrWhiteSpace($valueofdeploymentsettingstaging)) {
Write-Host “No app settings found in staging slot. We will give the deployment setting an empty output.”
$valueofdeploymentsettingstaging = “”
} else {
Write-Host “Value of deployment settings is $valueofdeploymentsettingstaging. We will continue with creating the backups”
}

$StagingDatabaseNameCore = $SourceDatabaseNameCore + $valueofdeploymentsettingstaging
$StagingDatabaseNameMaster = $SourceDatabaseNameMaster + $valueofdeploymentsettingstaging
$StagingDatabaseNameWeb = $SourceDatabaseNameWeb + $valueofdeploymentsettingstaging

### CORE Database export
Write-Host “staging Core db $StagingDatabaseNameCore will be exported to sqlbackup storage”
# Generate a unique filename for the Core BACPAC
$bacpacFilenameCore = $StagingDatabaseNameCore + (Get-Date).ToString(“yyyy-MM-dd-HH-mm”) + “.bacpac”
$bacpacFilenameCoreFullName = “Daily/$bacpacFilenameCore”
# Storage account info for the Core BACPAC
$BaseStorageUri = ‘https://’ + $AzureBlobStorageNameSqlBackup + ‘.blob.core.windows.net/’ + $ContainerName + ‘/’
$BacpacUriCore = $BaseStorageUri + ‘EveryDeploy/’ + $bacpacFilenameCore
$StorageKeytype = “StorageAccessKey”
$StorageKey = $Keys[0].Value
$sqlServerPasswordsecure = ConvertTo-SecureString -String $sqlServerPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlusername, $sqlServerPasswordsecure
$swCore = [system.diagnostics.stopwatch]::startNew()
$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupPaas -ServerName $SourceServerName -DatabaseName $StagingDatabaseNameCore -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUriCore -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
# Check status of the Core export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write(“$($exportStatus.Status)”)
while ($exportStatus.Status -eq “InProgress”)
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write(“Job has been running for $($swCore.Elapsed.TotalSeconds) seconds`n”)
[Console]::Write(“Status: $($exportStatus.Status)`n”)

Start-Sleep -s 20
}
[Console]::WriteLine(“”)

if ($($exportStatus.status) -eq ‘Failed’)
{
Write-Error “Database export $($exportStatus.status) does not match Succeeded, script will stop.”
}

else
{
Write-Host “Database export status $($exportStatus.status)”
$swCore.Stop()
Write-Host “Job total complete time is $($swCore.Elapsed.TotalSeconds)”
Write-Output “Azure SQL DB Core file path within sqlbackups container is $bacpacFilenameCoreFullName”
Write-Output (“##vso[task.setvariable variable=sqlCoreExportToStorageFileName;]$bacpacFilenameCoreFullName”)
}

### Master Database export
Write-Host “staging Master db $StagingDatabaseNameMaster will be exported to sqlbackup storage”
# Generate a unique filename for the Master BACPAC
$bacpacFilenameMaster = $StagingDatabaseNameMaster + (Get-Date).ToString(“yyyy-MM-dd-HH-mm”) + “.bacpac”
$bacpacFilenameMasterFullName = “EveryDeploy/$bacpacFilenameMaster”
# Storage account info for the Master BACPAC
$BaseStorageUri = ‘https://’ + $AzureBlobStorageNameSqlBackup + ‘.blob.core.windows.net/’ + $ContainerName + ‘/’
$BacpacUriMaster = $BaseStorageUri + ‘Daily/’ + $bacpacFilenameMaster
$StorageKeytype = “StorageAccessKey”
$StorageKey = $Keys[0].Value
$sqlServerPasswordsecure = ConvertTo-SecureString -String $sqlServerPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlusername, $sqlServerPasswordsecure
$swMaster = [system.diagnostics.stopwatch]::startNew()
$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupPaas -ServerName $SourceServerName -DatabaseName $StagingDatabaseNameMaster -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUriMaster -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
# Check status of the Master export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write(“$($exportStatus.Status)”)
while ($exportStatus.Status -eq “InProgress”)
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write(“Job has been running for $($swMaster.Elapsed.TotalSeconds) seconds`n”)
[Console]::Write(“Status: $($exportStatus.Status)`n”)

Start-Sleep -s 20
}
[Console]::WriteLine(“”)

if ($($exportStatus.status) -eq ‘Failed’)
{
Write-Error “Database export $($exportStatus.status) does not match Succeeded, script will stop. ”
}

else
{
Write-Host “Database export status $($exportStatus.status)”
$swMaster.Stop()
Write-Host “Job total complete time is $($swMaster.Elapsed.TotalSeconds)”
Write-Output “Azure SQL DB Master file path within sqlbackups container is $bacpacFilenameMasterFullName”
Write-Output (“##vso[task.setvariable variable=sqlMasterExportToStorageFileName;]$bacpacFilenameMasterFullName”)
}

### Web Database export
Write-Host “staging Web db $StagingDatabaseNameWeb will be exported to sqlbackup storage”
# Generate a unique filename for the Web BACPAC
$bacpacFilenameWeb = $StagingDatabaseNameWeb + (Get-Date).ToString(“yyyy-MM-dd-HH-mm”) + “.bacpac”
$bacpacFilenameWebFullName = “Daily/$bacpacFilenameWeb”
# Storage account info for the Web BACPAC
$BaseStorageUri = ‘https://’ + $AzureBlobStorageNameSqlBackup + ‘.blob.core.windows.net/’ + $ContainerName + ‘/’
$BacpacUriWeb = $BaseStorageUri + ‘EveryDeploy/’ + $bacpacFilenameWeb
$StorageKeytype = “StorageAccessKey”
$StorageKey = $Keys[0].Value
$sqlServerPasswordsecure = ConvertTo-SecureString -String $sqlServerPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlusername, $sqlServerPasswordsecure
$swWeb = [system.diagnostics.stopwatch]::startNew()
$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupPaas -ServerName $SourceServerNameWeb -DatabaseName $StagingDatabaseNameWeb -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUriWeb -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
# Check status of the Web export
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write(“$($exportStatus.Status)”)
while ($exportStatus.Status -eq “InProgress”)
{
$exportStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
[Console]::Write(“Job has been running for $($swWeb.Elapsed.TotalSeconds) seconds`n”)
[Console]::Write(“Status: $($exportStatus.Status)`n”)

Start-Sleep -s 20
}
[Console]::WriteLine(“”)

if ($($exportStatus.status) -eq ‘Failed’)
{
Write-Error “Database export $($exportStatus.status) does not match Succeeded, script will stop. ”
}

else
{
Write-Host “Database export status $($exportStatus.status)”
$swWeb.Stop()
Write-Host “Job total complete time is $($swWeb.Elapsed.TotalSeconds)”
Write-Output “Azure SQL DB Web file path within sqlbackups container is $bacpacFilenameWebFullName”
Write-Output (“##vso[task.setvariable variable=sqlWebExportToStorageFileName;]$bacpacFilenameWebFullName”)
}

View script

Be First to Comment

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.