In this blog post we will see one of sql server 2014 new features, Backup SQL Server 2014 database to Windows Azure cloud storage.
Let us first create a storage account to store the backup. Click on Storage then New, Cloud Service, Quick Create, then type the desired storage name in the URL box and select your region.
Now that we create a storage let us create a container for our backup. Click on Add at the bottom of the screen then on the new dialog type the name of the container and select access type. Finally click on accept button at the bottom right.
Notice the URL of the container we will be using that later for your script to backup our database.
Now open up your SQL Server Management studio and let’s work on the script. We first have to create a credential on the database we want to create. The identity will be the storage account name we created before with is ‘dbandbi’ for our case and to get the secret key go back to your windows azure management portal, then in the storage section click on manage access keys. Then copy either the primary or secondary access key. That will be your secret key.
--create credential-- create credential AzureCredential with identity = 'dbandbi', secret = 'AnOzW0xjQ3cIKoM23C8Y7y2vITkqd0zv1s2J9NlpuAvegAlu/9kBRwjwOmiEHGjWNIsDWusRSSbakGsm/rn84w=='; go --check credential-- select * from sys.credentials
This time we will specify our backup script as our database name that we created our credential, the url will be the container url that we created in the earlier step plus the backup blob name; and we will use the credential we created in the previous step.
--backup to windows azure backup database demodb to url = 'http://reckonedforce.blob.core.windows.net/backup/demodb.bak' with credential = 'AzureCredential' go
You will get a confirmation message like the above. Now that we finish backing up let us go back to the management portal and check if it is really backed up our database.
As you could see above our database is backed up indeed and stored in the container we created before.
If you are also interested to see how to backup SQL Server 2014 database from Windows Azure VM to Windows Azure storage check out this blog.