A script to export sql azure databases.

My context for the script in this case.

  1. A bug from the client is reported. A bug that does not cause exception at the back-end nor at the front-end – The font-end simply stops. It does not even freeze. So you do not have any log for this case. Now you are in check ( chess check ).
  2. You need to dump the database from the staging environment ( NO! Not the production! )

The possible solutions :

  1. Go to azure portal and export all the databases needed by hand – click-click-click….a lot.
  2. Make a powershell script and do it really quick. This helps a lot when you do it more then once.

The script :

# The command will prompt only once for most parameters during one session
[string] $azSubscriptionId, 
[string] $azStorageName, 
[string] $azStorageKey, 
[string] $azContainerName,
[string] $azSqlServerName, 
[string] $azSqlServerUserName, 
[string] $azSqlServerPassword)

#if you want to clean up all globally created vars => Remove-Variable -Name 'az*'
 Remove-Variable -Name 'az*'

$azSubscriptionId = "..."
$azStorageName = "..."
$azStorageKey = "..."
$azContainerName = "..."
$azSqlServerName = "..."
$azSqlServerUserName = "..."
$azSqlServerPassword = "..."
#Remove-Variable -Name 'azAccount' # clean up acc credentials. Can be removed but once in a while the credentials for the account get expired
#region azAccount
$azAccount = Get-AzureAccount
       Write-Output "No azure account. Prompting ..."
       $azAccount = Add-AzureAccount
    } While (!$azAccount)
    Set-Variable -Name azAccount -Value $azAccount -Scope Global
    $azAccount = Get-AzureAccount
#region azSubscriptionId
        Write-Output "No subscription Id. Prompting ..."
        $azSubscriptionId = Read-Host "Subscription Id"
    } While (!$azSubscriptionId)
    Write-Output "Input for subscription Id $azSubscriptionId"
    Set-Variable -Name azSubscriptionId -Value $azSubscriptionId -Scope Global
Select-AzureSubscription -SubscriptionId  $azSubscriptionId
Write-Output "Selected subscription Id $azSubscriptionId"
#region azStorageName
        Write-Output "No storage name. Prompting ..."
        $azStorageName = Read-Host "storage name"
    } While (!$azStorageName)
    Set-Variable -Name storageName -Value $azStorageName -Scope Global
#region azStorageKey
        Write-Output "No storage key. Prompting ..."
        $azStorageKey = Read-Host "storage key"
    } While (!$azStorageKey)
    Set-Variable -Name azStorageKey -Value $azStorageKey -Scope Global
#region azContainerName
        Write-Output "No container name. Prompting ..."
        $azContainerName = Read-Host "container name"
    } While (!$azContainerName)
    Set-Variable -Name azContainerName -Value $azContainerName -Scope Global
#region azSqlServerName
        Write-Output "No sql server name. Prompting ..."
        $azSqlServerName = Read-Host "sql server name"
    } While (!$azSqlServerName)
    Set-Variable -Name azSqlServerName -Value $azSqlServerName -Scope Global
#region azSqlServer
    Write-Output "No sql server variable stored on this PC. Prompting ..."
    $azSqlServer = Get-AzureSqlDatabaseServer -ServerName $azSqlServerName 
    Set-Variable -Name azSqlServer -Value $azSqlServer -Scope Global
#region azSqlServerCredenials
        Write-Output "No sql server credentials. Prompting ..."
        $azSqlServerCredenials = Get-Credential "Enter Credentials for $azSqlServerName"
    } While (!$azSqlServerCredenials)
    Set-Variable -Name azSqlServerCredenials -Value $azSqlServerCredenials -Scope Global
#region Sql connection context
    Write-Output "No Sql Server Context. Creating..."
    $azSqlCtx = New-AzureSqlDatabaseServerContext -ServerName $azSqlServer.ServerName -Credential $azSqlServerCredenials
    Set-Variable -Name azSqlCtx -Value $azSqlCtx -Scope Global
     Write-Output "Sql Server Context for $azSqlServer.ServerName created."
#region Storage connection context
    Write-Output "No Storage Context. Creating..."
    $azStorageCtx = New-AzureStorageContext -StorageAccountName $azStorageName -StorageAccountKey $azStorageKey
    Set-Variable -Name azStorageCtx -Value $azStorageCtx -Scope Global
    Write-Output "Storage context for $azStorageName created."
#region Storage container ref
    Write-Output "No container ref. Creating for $azContainerName"
    $azStorageContainer = Get-AzureStorageContainer -Name $azContainerName -Context $azStorageCtx
    Set-Variable -Name azStorageContainer -Value $azStorageContainer -Scope Global
    Write-Output "Container ref to $azStorageContainer created."
#region Sql Databases ref
    Write-Output "No Sql Databases array ref. Creating..."

    #$azSqlServerDatabases = Get-AzureSqlDatabase -ConnectionContext $azSqlCtx
#because from some time on the first way of gettign the dbs started to throw exceptions for 
#no apparent reason
#use this
# or this 
    #$azSqlServerDatabases = Get-AzureSqlDatabase -ServerName $azSqlServer.ServerName
# or this 
    $azSqlServerDatabases = Get-AzureSqlDatabase -ServerName $azSqlServerName

    Set-Variable -Name azSqlServerDatabases -Value $azSqlServerDatabases -Scope Global
    Write-Output "Sql Databases array ref created."
#region actual export of azure databases
$exportRequests = New-Object 'System.Collections.Generic.List[Microsoft.WindowsAzure.Commands.SqlDatabase.Model.SqlDatabaseServerOperationContext]'
Write-Output "Exporting databases"
foreach ($db in $azSqlServerDatabases) {
    $exportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $azSqlCtx -StorageContainer $azStorageContainer -DatabaseName $db.Name -BlobName ($db.Name + ".bacpac") 
    Write-Output ($db.Name + ".bacpac") 
#import dbs back into your local server

#$goodlist = dir
# probably the correct path
##C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120
#cd 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin'
#foreach($i in $goodlist){ $name = $i.Name; $namer = $i.Name.Substring(0, $i.Name.length - 7); .\SqlPackage.exe /a:Import /sf:[FOLDERPATH]\$name /tdn:$namer /tsn:[SERVERNAME] }

This is it. If you can elaborate on it and make it automatically restore the dbs in SqlExpress, feel free to post it as a comment.


I started to encouter strange but persistent error :

PS C:\Windows\system32> Get-AzureSqlDatabase -ConnectionContext $azSqlCtx
WARNING: Client Session Id: ‘…’
WARNING: Client Request Id: ‘…’
Get-AzureSqlDatabase : Internal .Net Framework Data Provider error 6.
At line:1 char:1
+ Get-AzureSqlDatabase -ConnectionContext $azSqlCtx
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [Get-AzureSqlDatabase], InvalidOperationException
+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.GetAzureSqlDatabase

There is an article about this error here but I did not find any way to download the fix. Than I opened msdn where GetAzureSqlDatabase is described and used the “ByServerName” method.