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)
8 Comments. Leave new
This is great. I hadn’t seen that. That is convenient and easy and safe. Thanks for sharing this useful feature.
my please Dave.
After weeks messing with this I found that it only works with Classic storage accounts, and the backup will fail on Resource Manager storage accounts.
I am trying to take backup to URL but how can i generate new file name? getting below error
Msg 3271, Level 16, State 1, Line 2
A nonrecoverable I/O error occurred on file “mycblobstorage.blob.core.windows.net/sqlbackup/master.bak:” The file mycblobstorage.blob.core.windows.net/sqlbackup/master.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed..
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
I’m currently using this feature. Problem is, it’s backing up very often and whenever. Do you know if I can set a schedule for the backups to happen?
Thanks
Hi Pinal
i have a Large database backup about 12 TB, after compression, i want to use the Backup to URL, but its not allowing me to split the backup files, and i think there is a limit of 1TB file to Azure?
is there anyway i can backup to URL when the backup file is Huge?
Regards
How do you automatically update the credential when SAS is regenerated