June 06, 2019

How to restore a SQL Azure database locally

Hey there 👋 A quick interruption before you start reading.

I've been working on a new project called StellarAdmin that helps ASP.NET Core developers like you rapidly create admin screens for your application's Admin and Support users. If this sounds like something that will save you time on your projects, please do me a favour and check it out🙏🙏🙏

This blog post will take you through the process of creating a backup of a SQL Azure database and restore it to a LocalDB database on your computer using the SqlPackage tool.

Installing the SqlPackage tool

Before you get started, you will need to ensure that you have the SqlPackage tool installed. In my case, this was installed as part of Visual Studio 2019, and I could find the sqlpackage.exe tool under the C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150 folder. This may be different for you, but, given that you have chosen to install the SQL Server tools with Visual Studio, you’ll probably find in a similar folder.

If you cannot find it, you can follow this blog post to download and install it.

Creating the backup on Azure

To export the database, you can go the Overview page for your database in the Azure Portal. Click on the Export link at the top:

This will open a blade that will allow you to configure the export. Supply the settings as required, and click on OK.

This will schedule a job that will export the database and store it in the Blob container as per your export settings. You can track the progress of the export by going to the Import/Export History screen of your database server.

Once the export has completed you can download it from the container you specified in the export settings. In my case, I used the Azure Storage Explorer to do the download.

Restore the export

Now that you have downloaded the export, you can use the SqlPackage command to restore it. Open your command-line of choice (Powershell in my case) and run the following command:

.\sqlpackage.exe /Action:Import /SourceFile:"C:\Users\jerri\Downloads\database-backup.bacpac" /TargetConnectionString:"Data Source=(localdb)\mssqllocaldb;Initial Catalog=CloudpressBackup;Integrated Security=true;"

The SourceFile parameter should be the path to where you saved the database export on your computer. The TargetConnectionString should be the correct connection string for your database.

I hope this helps!