SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio

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.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups1

As soon as the URL is selected, it would ask for various options about backup destination in Azure.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups2

Let’s go step by step and take a database backup to Microsoft Azure Blob storage.

Solarwinds

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.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups3

Under sqlauthority, I have created a container called “backup”. This container was created as “Public Blob”.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups4

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.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups5

Once we have the secret key, we can go to SSMS and right click on “Credential” under “Logins” to choose “New Credential”.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups6

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.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups7

Once the backup is complete, we should see the backup on the portal. Notice URL

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups8

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.

SQL SERVER - Backup to Azure Blob using SQL Server 2014 Management Studio azurebackups9

Go ahead and play with this feature.Let me know what you think of the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – SQL Server Data Compare Tool
Next Post
SQL SERVER – In-Memory Databases and Caching Wars

Related Posts

7 Comments. Leave new

  • This is great. I hadn’t seen that. That is convenient and easy and safe. Thanks for sharing this useful feature.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
    • If you use “managed backup” then SQL would manage it based on its own logic. You can use maintenance plan to schedule them if you don’t like SQL to manage it for you.

      Reply
  • 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

    Reply

Leave a Reply

Menu