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.
The restore function states success, but after editing the data and restoring nothing changes so this seems not to work at all.
Hi,
It seems you use a very old version of SQLBackupAndFTP. Could you please install the latest release – version 12.3.8 and check if it works for you?
does it work for managed instances also
Hi, could you please clarify your question?
Can you please update the Azure screenshots ? (the one listed in website is very old)
Hello,
Sure, we have updated the screenshots, thank you.
“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.”
Does SQLBackupAndFTP automatically create this DB copy for us? And if so does it automatically delete it after?
Hi Tom,
Yes, it does, if the “Create database snapshot for backup” option is enabled. It can be found at the “Backup options” section.
When I backup the Azure SQL to Dropbox, do I need to keep the local computer is ON?
And also when it is backing up the Azure SQL in schedule, do I keep the local computer is ON?
I think if the local computer is OFF, the scheduler won’t work properly, is this correct?
Hi Simon,
Yes, you are right. For the correct work of the application, a computer where SQLBackupAndFTP is installed should be on.
I need to backup one of our Azure databases for tape archive on the 1st of every month. The scheduler does not seem to accommodate that scenario. Did I miss something or is that an option that may be added in the future?
Hi Steve,
Sorry, but currently there is no way to do it, but such an option will be added to the schedule settings soon.
Sorry for the inconvenience.
My ERP is in Azure and everyday morning it create a backup of the databases in a folder. I want to download that folder to a machine in my premises every day. What do you recomend?
Best regards
Hi Carlos,
Could you please tell us more about the technology stack that you use? Do you mean Azure under ERP and under folder – azure blob storage?
Thank you!
The ERP is installed in a virtual machine in Azure. It use MS SQL. Every day it makes a security copy of the DBs for a folder.
I want to download that copies for a machine in my premises. Before to migrate to cloud, we use the SQLBAK to make a copy of that folder in a NAS in another building.
Regards
Hi Carlos,
Sorry, there is no way to do it, but you can use SqlBak with the following workaround. Please install the SqlBak app on the server in Azure, make a backup of a database, and then perform a restore on your server (the SqlBak app must also be installed on that server) After that, you can backup the database on that server.
We have a 70GB azure sql database. Have you tested your product on a database of that size?
We used to have custom code that would create the bacpac and copy it but that code now gets Out of Memory errors. Even if I try to manually create bacpac from SSMS I get out of memory errors. So we are looking for something that can handle that size db.
Thanks.
Hi Ryan,
We tested 200 GB database backups for functionality but did not pay much attention to performance, as there were no obvious issues.
Hello
Are there any plans to support Service Principals / Managed Identities to authenticate from SQLBackupandFTP to Azure SQL instead of SQL User?
Thanks
Hello,
Thank you for your request; it has been forwarded to our development team for consideration.
Should you have any further questions, please don’t hesitate to ask.
We appreciate your usage of SQLBackupAndFTP.
Hello,
Is it possible to specify the database name into the connexion script because I have no access to master database. I’m getting an error message on test or database selection screen:
not able to access the database “master”
Because my user only have access to one database not to all
regards
Hello Franck,
Thank you for your message.
Currently, SQLBackupAndFTP does not have this feature. However, we are considering adding it in future releases.
Thank you and apologies for the inconvenience.
Best regards,
Oleg