Download Backup and Restore a Azure SQL Database Locally for Testing

Azure SQL Database is Microsoft’s Database as a service on the cloud. While your production database might reside on the cloud, developers might want a local copy of the Database on their local SQL Server. If you are someone who is looking for options to copy your Database locally then there are several options available. We have collated a list of options which we think are based suited for this purpose. Read along to find out the various options available to download Azure SQL Database Locally for Testing.

Recommended option to download Azure SQL Database Locally for Testing

The best option to download a local copy is to use the “Export” option available on the Azure portal. This option will create an exact copy of your Azure SQL Database to a “bacpac” file. This method will ensure that your database “Column properties“, “constraints  definition “,  keys “PK and FKs“, stored procedures and triggers are also retained. This is also a recommended approach from Microsoft for Database Migration from Cloud to On-Premises. In order to migrate the Azure SQL Database locally, perform the following steps:

Also Read: How to Backup an Azure SQL Database

• Step 1: Logon to the Azure Management Portal and go to your Database properties.
• Step 2: Select the “Export” option from the top menu bar.
• Step 3: Under “Export Database” section, type in a filename and the storage account where you want to export the bacpac file. Exporting to a storage account in the same location is recommended due to performance considerations.
• Step 4: Type in the Azure SQL Username/Password and click on “OK“.
• Step 5: Download the bacpac file using Azure Storage explorer. Download the Azure Storage Explorer here.
• Step 6: Import the bacpac file into your local SQL Server.

See the screenshots below for your reference:

Steps 1,2,3 & 4:

Step 5: Select the Storage account and the container to download the bacpac file

Step 6: Import the bacpac file into SQL Server installed locally

If you are using the latest version of SQL Server Management Studio, then you can ignore “Step 5” and directly import the file from your Azure Blog Storage. See the second option in the screenshot above.

Other Methods to download Azure SQL Database Locally for Testing

• SQL Server Integration Services (SSIS) : ETL your data using the Import/Export wizard with Azure SQL Database as your source and Local SQL Server as your destination. However, your logins, Constraints, Stored Procedures, Relationships are not transferred and everything needs to be done manually.
• DB Creation Scripts: Manually create Database scripts and run them on your local server.

Hope this was helpful. Do let us know your feedback in the comments section.