Copying Windows Azure SQL Database to a local server

The following script will download a SQL Azure database and import it into a local running instance. It assumes the local database does not exist. The import step could also be used to create an off-site backup.

This script uses the SqlPackage tool, which can be downloaded using the Web Platform Installer to install Microsoft SQL Server Data-Tier Application Framework (DACFx).

You will need to replace the values surrounded in asterisks (*) for the script to work correctly.

param([string][parameter(mandatory)]$DatabaseAdministratorPassword)

$sqlPackage = 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe'

$bacPacFile = join-path $env:TEMP 'Import.bacpac'
try 
{
    & $sqlPackage /a:Export /ssn:*****AZURE_DATABASE_SERVER***** /sdn:*****DATABASE_NAME***** /su:*****AZURE DATABASE ADMIN USER NAME***** /sp:$DatabaseAdministratorPassword /tf:$bacPacFile
    if(!$?) {
        throw "Failed to export database."
    }
    & $sqlPackage /a:Import /tdn:*****DATABASE_NAME***** /tsn:localhost\sqlexpress /sf:$bacPacFile
    if(!$?) {
        throw "Failed to import database."
    }
}
finally {
    del $bacPacFile
}

Getting more detail from Azure PowerShell Cmdlet 400 errors

Some of the Azure PowerShell Cmdlets return less than useful errors:

New-AzureDeployment : The remote server returned an unexpected response: (400) Bad Request.
At line:1 char:5
+     New-AzureDeployment -Slot $slot -Package "$root\NewSite.Azure\bin\Release\ap ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [New-AzureDeployment], ProtocolException
    + FullyQualifiedErrorId : Microsoft.WindowsAzure.Management.ServiceManagement.HostedServices.NewAzureDeploymentCommand

The way to get more detailed information is on this blog:

(New-Object System.IO.StreamReader($Error[0].Exception.InnerException.Response.GetResponseStream())).ReadToEnd()

I have simply formatted this into a useful PowerShell oneliner. Note that this command can only be run once. The problem in my case was that I had changed my storage account to camel case, but it must be all lower case.

<Error xmlns="http://schemas.microsoft.com/windowsazure" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <Code>BadRequest</Code>
  <Message>The name is not a valid storage account name. Storage account names must be between 3 and 24 characters in length and use numbers and lower-case letters only.</Message>
</Error>