How do I copy SQL Azure database to my local development server?

I think it is a lot easier now.

1. Launch SQL Management Studio
2. Right Click on "Databases" and select "Import Data-tier application..."
3. The wizard will take you through the process of connecting to your Azure account, creating a BACPAC file and creating your database.

Additionally, I use Sql Backup and FTP (

[To see links please register here]

) to do daily backups to a secure FTP server. I simply pull a recent BACPAC file from there and it import it in the same dialog, which is faster and easier to create a local database.

If anyone has a problem to import a Bacpac of a DB that uses **Azure SQL Sync**, Sandrino Di Mattia developed a [great simple application][1] to solve this.

1. Export a Bacpac of your DB
2. Dowload Di Mattia's [binary][2]
3. With this console app repair the downloaded Bacpac
4. Lauch SSMS
5. Right Click on "Databases" and select "Import Data-tier Application"
6. Select the repaired Bacpac.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

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:

[To see links please register here]

. 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:

[To see links please register here]

.

It's pretty easy. This worked for me...in terms of getting an Azure SQL database down onto your local machine...:

1. Open your SQL Management Studio and connect to your Azure SQL Server.
2. Select the database you would like to get down onto your local machine, and right-click...select "Generate Scripts". Follow the prompts...

BUT, be careful in that if you ALSO want the DATA, as well as the scripts, be sure to check the Advanced Options before beginning the generating...scroll down to "Types of data to script", and make sure you have "Schema and data"...or whatever is appropriate for you.

It will give you a nice SQL script file which can then be run on your local machine and it will create the database as well as populate it with all the data.

Bare in mind that in my case, I have no FK or other constraints...also, it wasn't a lot of data.

I don't recommend this as a backup mechanism in general...

You can try with the tool "SQL Database Migration Wizard".
This tool provide option to import and export data from azure sql.

Please check more details here.

[To see links please register here]

You can also check out [SQL Azure Data Sync][1] in the Windows Azure Management Portal. It allows you to retrieve and restore an entire database, including schema and data between SQL Azure and SQL Server.


[1]:

[To see links please register here]

If anyone wants a free and effective option (and don't mind doing it manually) to backup database to Local then use schema and data comparison functionality built into the latest version Microsoft Visual Studio 2015 Community Edition (Free) or Professional / Premium / Ultimate edition. It works like a charm!

I have BizPark account with Azure and there is no way to backup database directly without paying. I found this option in VS works.

Answer is taken from

[To see links please register here]

Hi I'm using the SQLAzureMW tool for SQLAzure DB migration and management. Very useful one. It was downloaded from codeplex, but currently it's not available the codeplex will be going to shutdown, the same application tool is now available in GttHub. This below link is explain how to use this tool and also available the Application for download.

[To see links please register here]

In SQL Server 2016 Management Studio, the process for getting an azure database to your local machine has been streamlined.

Right click on the database you want to import, click **Tasks > Export data-tier application**, and export your database to a local .dacpac file.

In your local target SQL server instance, you can right click **Databases > Import data-tier application**, and once it's local, you can do things like backup and restore the database.

Copy Azure database data to local database:
Now you can use the SQL Server Management Studio to do this as below:

- Connect to the SQL Azure database.
- Right click the database in Object Explorer.
- Choose the option "Tasks" / "Deploy Database to SQL Azure".
- In the step named "Deployment Settings", connect local SQL Server and create New database.

![enter image description here][1]

"Next" / "Next" / "Finish"


[1]: