How to backup Azure SQL Database to Local Machine

Being a managed cloud database Azure SQL Database provides built-in functionality for automatic database backups. It allows you to restore your database to any point in the past and those backups are kept in reliable geo-redundant storage. So why do we need other tools like SqlBackupAndFtp to backup of Azure SQL Database? This can be the case when you need to get a copy of your database locally, outside Azure infrastructure. In this post, you can find a step-by-step guide on how to backup the Microsoft Azure SQL Database with the help of SQLBackupAndFTP.

Backup vs Export

Microsoft calls this procedure Azure Database Export and it significantly differs from the built-in backups at least because it doesn’t save transaction logs and that makes the point-in-time recovery impossible. Nevertheless, it used to be the only way to backup Azure SQL Database when the built-in backup capabilities were not available yet.

The export procedure produces a BACPAC file, which is intrinsically a ZIP file containing the metadata and data from an Azure SQL database. You can download this BACPAC file locally and later import it back to Azure or even into a SQL Server on-premises installation.

It is necessary to mention that, to guarantee transaction consistency a copy of your Azure Database will be created during the export process. This copy is treated as a second database and is billed as such.  So if your database is big enough it might take significant time and money to export it locally.


How to backup Microsoft Azure Cloud SQL Database

1. Connect SQLBackupAndFTP to the logical SQL Server in the Azure

Go to your “Microsoft Azure Dashboard” page and choose the logical SQL Server in the Azure:

You can see information about your server after clicking on the server’s name on the Dashboard:

See your server’s name under “Properties”.

Run SQLBackupAndFTP and click plus button to create a new backup job.

Click the gear button next to “Connect to Database Server”, select “Azure SQL Server” as a Server type, enter your Server’s name, your User name, and Password that you have specified during the Azure logical SQL Server creation:

Push the “Test Connection” button, you have to see a message that the connection test succeeded.

2. Create a job for regular Azure SQL Database backup

After you have established the connection to your Azure SQL Server select the databases you want to backup. To find the database’s list click on the gear button at the “Selected Databases” section and check on databases you need to backup.

Then go down to the “Store backups in selected destinations” section and click the plus button to select a place where you prefer to store your Azure SQL Database backups. Assume, you prefer to store the backups in Azure Storage. Choose “Azure Storage” from the list:

Once you click “Azure Storage” the “Windows Azure Storage Settings” window opens where you need to enter your credentials.

You have to prepare your Azure Storage Account and see it on the “Microsoft Azure Dashboard page”.

If you click on your Storage account name, you will see a picture like this:

Click “Access keys” and find the primary access key there:

Select or create a new container if necessary:

So, these are the data to fill into the SQLBackupAndFTP window of Windows Azure Storage Settings.

To check the connection click “Test”, then press the “Save & Close” button to apply all settings.

Turn on “Schedule backups”  to create a backup schedule. Click on the gear button to find additional settings

That’s it. You have just set up scheduled Azure SQL Database backups!

How to restore Microsoft Azure Cloud SQL Database

The restore process is even easier than the backup process! Find the backup you need to restore from the list on the right side of the application. Click on the button next to backup and select “Restore from Backup…”.

In the opened window click on the “Restore” button.

Check the settings and click on the “Restore” button.

After the restore  process is completed, you’ll receive the following message:

Here you are! You have just restored Azure SQL Database in a few clicks.