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
}