How to Copy Azure SQL Database to Local SQL Server

By | September 5, 2017

Azure SQL Database is a relational database-as-a service using the Microsoft SQL Server Engine. SQL Database is a high-performance, reliable, and secure database you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure.

Some times you may want to create a copy of azure database on you local server. So that you can use it for development and bugging activities.

Here’s what you do when you want to get a local copy of an Azure SQL Database.

Export Azure Database

  1. Select your database in the Azure Portal.
  2. Click Export in the Header
  3. Give it a name, storage, container, etc and start the export.
  4. Once export is completed. Select your server and scroll to the bottom and click on Import/Export History

Import to Local SQL Server

Once the Export has completed, you now Import it to your local machine. This works with SQL Express too, just install the BOTH x86 and x64 versions of the DAC Framework first: https://www.microsoft.com/en-us/download/details.aspx?id=45886

  1. Open SQL Management Studio and Right Click on Databases and Select Import Data-tier Application
  2. Connect to your Azure Database Export and click Next a couple of times.

That should be it. If it fails, make sure you install the DAC Framework as mentioned above.

If you are more comfortable with the CLI, then you can use following commands to restore a local copy Azure SQL DB on you local SQL Server.

Using msdeploy.exe

msdeploy.exe fails to create the destination database on its own, so you need to create it manually first.

  1. Copy the connection string on the database properties page. Adjust it so that it contains a correct password.
  2. Get the connection string for the destination DB.
  3. Run msdeploy.exe like this:
"c:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:sync -dest:dbDacFx="destination_DB_connection_string",dropDestinationDatabase=true -source:dbDacFx="azure_DB_connection_string",includeData=true -verbose

Using SqlPackage.exe

  1. Export the azure DB to a bacpac package.
"c:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:Export /ssn:"azure_db_server" /sdn:"azure_db_name" /su:"user_name" /sp:"password" /tf:"file.bacpac"

2. Import the package to a local DB.

"c:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:Import /SourceFile:"file.bacpac" /TargetServerName:".\SQLEXPRESS" /TargetDatabaseName:CopyOfAzureDb

Leave a Reply

Your email address will not be published. Required fields are marked *