How to copy SQL Azure database to local development server?

SQL Azure Database provides all the essential database creation, querying, testing and optimization tools from the Azure Cloud Service Platform. However, there are times when you do need to work on database hosted on Azure from your local development server.

So how would you copy and transfer database from SQL Azure to your locally hosted SQL Server? This article will illustrate a few simple ways to achieve this.

Using SQL Server Integration Services (SSIS) Wizard

SSIS provides very simple means to copy SQL Azure Database to your onsite SQL Server console. Just follow the simple process guided by the wizard in SQL Server Management Studio. But using this service will only import data from the table not column properties, constraints, keys, indices, stored procedures, triggers, security settings, users, logons, etc.

Copying Azure SQL Database manually using SSIS

First create a new empty database on your local SQL instance in SQL Server Management Studio

Choose Import Data from menu.

Write the connection parameters for the source (SQL Azure). Being created for local server, select “.Net Framework Data Provider for SQLServer” as a provider.

Now choose the newly created empty database as the destination.

Furthermore, you can follow the wizard to select table’s data you want to copy. You can choose to skip any of the tables you don’t need. And this process can also be automated by creating SSIS package and re-executing it whenever an import job is required.

Using a combination of SSIS and DB creation scripts

This process will not only copy data from tables ( as described above) but also will fetch all data and metadata not copied through SSIS.

UseImport/Export service in SQL Azure

By using the Import / Export service in SQL Azure, will transfer data with schema objects to Azure Blob Storage as a BACPAC. You will need an Azure Storage account and do this in Azure web portal. Your data is copied as BACPAC into Azure Blog Storage, just click export button from Azure web portal and select the SQL instance you want to export.