Recently, while working on a demo about backup, I realized that in management studio, now there is a new option in the backup screen called a URL. This feature is called as “backup to URL”.
I checked earlier version of SSMS and found that this was not there in the SQL 2012 management studio. There the options were “disk” and “tape” in SSMS 2012.
As soon as the URL is selected, it would ask for various options about backup destination in Azure.
Let’s go step by step and take a database backup to Microsoft Azure Blob storage.
To use this feature, you need to have is a Microsoft Azure account. If you do not have a Windows Azure account, visit Windows Azure 3-Month free trial. After logging it to the portal, I have created a “storage” called sqlauthority.
Under sqlauthority, I have created a container called “backup”. This container was created as “Public Blob”.
If we closely look at URL it is http://<StorageAccount>.blob.core.windows.net/<ContainerName> Going back to SSMS, this is what we have to provide. But wait, how would SQL Server connect to Azure Blob storage? Well, that’s where the credential comes into the picture. Note that authentication to the storage account is required for SQL Server backup and restore even if we choose to create a public container. There are multiple ways to connect to the storage – publishing profile, certificate or SQL Credential. To use SQL credential, we need to get the secret key from the portal using below the screen. The screen would appear once we click on “Manage Access Key” in the third image of this blog.
Once we have the secret key, we can go to SSMS and right click on “Credential” under “Logins” to choose “New Credential”.
Once this is done, we are all set. Go back to UI and choose “backup-cred” in the drop down. Here is the final screen.
Once the backup is complete, we should see the backup on the portal. Notice URL
Once the backup is available, we can also restore it. We can use management studio as well as T-SQL to restore the backup taken on the URL. While choosing the device, we need to use “URL” option, browse to storage container and choose correct file.
Go ahead and play with this feature.Let me know what you think of the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)
