What Azure SQL was missing was a proper supported way to make backups for disaster scenarios. Those scenarios would include loss of control over Azure SQL server or human error causing SQL admin to delete whole Azure SQL server. Great news everyone Azure SQL now has tools to mitigate the impact of these scenarios. SQL Azure Import/Export Service CTP is now available. More details on how this works can be found here.
What do you need?
You need means of scheduling backup, Azure Storage account where to put bacpac files, means to get the exact url of bacpac file and a Azure SQL account to backup.
You need one Azure account with Azure SQL and Storage server of course. 🙂
You might want to have separate backup storage account because, if you cannot access your production account you still have access to your backups. I personally download bacpac backups from Azure to local server.
Bacpac file export tool
In this example I will use Red-gate backup tool. This is because this tool allows you to easily make a database copy of your database before the backup. This will allow you to make transactionally safe backups.
Azure Storage account browser
You can use any tool you like. In this example I will use Azure Storage Explorer because it is free 🙂
Ideally this would be dedicated windows 2008 server so that you can be sure that it runs smoothly. You can also download exported bacpac files to this server. Just to be safe 🙂
Backup using Red-Gate command line backup tool
Here is a nice how to video how to setup scripts using Red-Gate tool. NOTE that in this video script will make a backup into different database. What we want to do is schedule a bacpac file generation. If you want to test how bacpac file generation works without command line watch this video.
But back to business: your scheduled script should look something like this:RedGate.SQLAzureBackupCommandLine.exe /AzureServer:[url_to_azure_server] AzureDatabase:[databasename] /AzureUserName:[db_owner_username] /AzurePassword:[password] /CreateCopy /StorageAccount:[Azure_account_name] /AccessKey:[primary_or_secondary_azure_storage_key] /Container:[container_name_in_storage] /Filename:[filename_of_bacpac]
Notice that I did not use any real values in above script. Just fill in the parameter between [ ] and schedule this script to as often you like. Note that you need to change the file name on every run because over writing with a same file name is does not work. I use date+time combinations.
So now we are ready for disasters 😉 Next I will explain how to perform a restore to totally new Azure SQL server.
Restore using Windows Azure management portal
When you need to restore a database to new server you need to have access to bacpac backup file in Azure storage account.
- Firstly create new Azure SQL server. How to video of that is here.
- Secondly and this is important add same database logins that the backedup database had. Azure SQL user management is explained in detail here.
- Thirdly get url of the pacbac file using Azure Storage Explorer. Open Azure Storage Explorer and login to storage account containing the bacpac file. Select the bacpac file and press view button. That is explained in the video of next step.
- Fourthly you are ready to restore! It can be done like explained in this video.
If you have any comments or questions please don’t hesitate to ask.
Thanks for reading and happy backupping!