How to copy Azure SQL database to a local development server?

+1 vote
Does anyone know how I can copy a SQL Azure database to my development machine? I'd like to stop paying to have a development database in the cloud, but it's the best way to get production data. I copy my production database to a new development database but I'd like to have that same database local.

Any suggestions?
Jun 21, 2018 in Azure by club_seesharp
• 3,450 points
253 views

1 answer to this question.

0 votes

Actually, there are multiple ways to do this:

  1. Using SSIS (SQL Server Integration Services). It only imports data in your table. Column properties, constraints, keys, indices, stored procedures, triggers, security settings, users, logons, etc. are not transferred. However it is very simple process and can be done simply by going through wizard in SQL Server Management Studio.
  2. Using combination of SSIS and DB creation scripts. This will get you data and all missing metadata that is not transferred by SSIS. This is also very simple. First transfer data using SSIS (see instructions below), then create DB Create script from SQL Azure database, and re-play it on your local database.
  3. Finally, you can use Import/Export service in SQL Azure. This transfers data (with a schema objects) to Azure Blob Storage as a BACPAC. You will need an Azure Storage account and do this in Azure web portal. It is as simple as pressing an "Export" button in the Azure web portal when you select the database you want to export. The downside is that it is only manual procedure, I don't know a way to automate this through tools or scripts -- at least the first part that requires a click on the web page.

Manual procedure for method #1 (using SSIS) is the following:

  • In Sql Server Management Studio (SSMS) create new empty database on your local SQL instance.
  • Choose Import Data from context menu (right click the database -> Tasks -> Import data...)
  • Type in connection parameters for the source (SQL Azure). Select ".Net Framework Data Provider for SqlServer" as a provider.
  • Choose existing empty local database as destination.
  • Follow the wizard -- you will be able to select tables data you want to copy. You can choose to skip any of the tables you don't need. E.g. if you keep application logs in database, you probably don't need it in your backup.

You can automate it by creating SSIS package and re-executing it any time you like to re-import the data. Note that you can only import using SSIS to a clean DB, you cannot do incremental updates to your local database once you already done it once.

Method #2 (SSID data plus schema objects) is very simple. First go though a steps described above, then create DB Creation script (righ click on database in SSMS, choose Generate Scripts -> Database Create). Then re-play this script on your local database.

Method #3 is described in the Blog here: http://dacguy.wordpress.com/2012/01/24/sql-azure-importexport-service-has-hit-production/. There is a video clip with the process of transferring DB contents to Azure Blob storage as BACPAC. After that you can copy the file locally and import it to your SQL instance. Process of importing BACPAC to Data-Tier application is described here: http://msdn.microsoft.com/en-us/library/hh710052.aspx.

answered Jul 9, 2018 by null_void
• 3,220 points

Related Questions In Azure

0 votes
2 answers

How to restore SQL Server .bak file to Azure SQL Database?

To restore SQL Server .bak file you ...READ MORE

answered May 27 in Azure by sherry
• 140 points
6,488 views
0 votes
1 answer

How to query between databases in SQL Azure Database Server?

SQL Azure supports cross database queries: https://azure.micros ...READ MORE

answered Apr 9 in Azure by Prerna
• 1,940 points
144 views
0 votes
2 answers

How can I download a .vhd image to my local machine from azure and upload the same to a different azure account?

From the Windows Azure Portal you can ...READ MORE

answered Aug 20, 2018 in Azure by Priyaj
• 56,900 points
3,050 views
0 votes
1 answer

How to identify a deadlock in SQL Azure?

Monitoring of SQL Azure is more limited ...READ MORE

answered Jun 19, 2018 in Azure by club_seesharp
• 3,450 points
363 views
0 votes
1 answer

How can we connect Azure Web App to an Azure SQL Database?

Its easy now! Go to your Azure SQL ...READ MORE

answered Aug 8, 2018 in Azure by club_seesharp
• 3,450 points
282 views
0 votes
1 answer

How to calculate the used database space in SQL Azure?

I don't think its possible to find ...READ MORE

answered Mar 5 in Azure by Archana
• 5,560 points
34 views
0 votes
1 answer

How do disconnect to Azure SQL database if I can't access it?

Based on info  from Microsoft, I think ...READ MORE

answered Apr 26 in Azure by Prerna
• 1,940 points
81 views
0 votes
1 answer

How can I copy SQL Azure database to a local development server?

There are certain ways to do this: Using SSIS ...READ MORE

answered Jun 26, 2018 in Azure by null_void
• 3,220 points
77 views
0 votes
4 answers

How can I rename a SQL Azure database?

This command serves the purpose ALTER DATABASE [oldname] ...READ MORE

answered Nov 30, 2018 in Azure by Abhinav
415 views