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.
[expander_maker id="1” more="View script” less="Collapse”]
#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”) } [/expander_maker]