Exporting Your Azure Databases – CloudShell

I decided to start this series of quick posts where I can show some scripts or cloud tips to help the admins on their daily tasks. To start it, let’s see how we can automate the export process of working with bacpac files.

Table of Contents

What is a bacpac file?

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database, Azure SQL Managed Instance, or a SQL Server instance.

1 – Exporting

This process is quite easy to be executed from the portal, but what if you had many SQL databases from many different servers to export?

This script will help you with the following:

Installs the SQLPackage needed to extract the bacpac from the cloud;
Creates the Firewall rule on each SQL server using your current public IP address;
Exports the databases using a CSV file that you prepared before;

It also has the following dependencies that will be installed on your machine:

A CSV file will be used to provide the script with the correct input, this way you don’t need to touch in any part of the code.

The CSV file looks like this:

Here is a copy of the CSV header and one line as an example:

name	resourcegroup	server	outputpath	sqluser	sqlpasswd
CloudshellDB	SQL-Tests	cloudshellsrv	C:\temp	user

After creating your csv file, you have to run the script:

#Path to your CSV file
$csv = Import-Csv "C:\temp\azDatabases.csv"

##TODO

#Dependencies

#If Az.Sql Module is not installed. download and install it.
$azmodule = "az.Sql"
if (-not (get-module -ListAvailable $azmodule)) {
    write-host "Installing $azmodule module..." -ForegroundColor Yellow
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
    Install-Module $azmodule -Force
}else{
    write-host "$azmodule is installed." -ForegroundColor Green
}

#If SQLPackage is not installed, download and install it.

$bin = "$binpath\SqlPackage.exe"
$binpath = "C:\Program Files\Microsoft SQL Server\150\DAC\bin"

if (Test-Path $bin) {
    write-host "SQL Package is installed" -ForegroundColor Green
    set-location $binpath
}
else {
        
    $sqlPackage = "https://download.microsoft.com/download/d/d/d/ddd3efcd-d5af-4a3f-947f-07520676e54f/x64/DacFramework.msi"
    $Filename = $sqlPackage.Split("/")[-1]
    write-host "SQL Package not found on your computer. Downloading it..." -ForegroundColor Yellow
    Invoke-WebRequest -Uri $sqlPackage -UseBasicParsing -OutFile "$env:TEMP\$Filename" 
        
    Write-Host "Installing SQL Package, please wait... " -ForegroundColor Yellow 
        
    $file = "$env:TEMP\$Filename" 
    $arguments = "/i `"$file`" /quiet"
    Start-Process msiexec.exe -ArgumentList $arguments -Wait

    Write-Host "SqlPackage installed." -ForegroundColor Green

    Set-Location $binpath
}     

foreach ($db in $csv){

#Creating a firewall exception to allow your ip to connect to the SQL server.
$myip = (wget ("ipconfig.me")).content

$fwrules = Get-AzSqlServerFirewallRule -ResourceGroupName $rg -ServerName $server
foreach ($fwrule in $fwrules) {
    if ($fwrule.startIpAddress -eq $myip) {
        write-host "The firewall rule already exists for your ip." -ForegroundColor Green
        $ruleexists = $true
        break
    }
    else {}
}

if (-not($ruleexists)) {
    Write-Host "Creating a Firewall exception for $myip..." -ForegroundColor Yellow
    Set-AzSqlServerFirewallRule -ResourceGroupName $rg -ServerName $server -FirewallRuleName "MyIP" -StartIpAddress $myip -EndIpAddress $myip
} 


    #Export the bacpac
    Write-Host "Exporting the bacpac to $($db.outputpath)"
    .\SqlPackage.exe /a:Export /tf:"$($db.outputpath)\$($db.name).bacpac" `
    /scs:"Data Source=$($db.server).database.windows.net,1433;User ID=$($db.sqluser);Password=$($db.sqlpasswd);Initial Catalog=$($db.name);"
}

When the script is running, you’ll see that after exporting one database, it is going to export another, and so on, as long as you have entered the correct information in the CSV file:

And that’s it, you can also find this script on my github. If you think this script can be improved, leave us a comment on the section below. See you in the next post!