How to use Azure Blog Storage for SQL Server Backup and Restore

By:   |   Comments (3)   |   Related: > Azure Backup and Restore


During an Azure migration, I faced a challenge when trying to migrate our large on-premises database to an Azure IASS server. We were configuring Always On between the on-premises and Azure server. The database was more than 500GB and taking backups from a local share and restoring on to the Azure DR server was taking more than 24 hours and it failed a few of times as well.  How can this be improved?


Taking a local backup and restoring it to an Azure DR server with an ETA of more than 24 hours was not acceptable as we couldn't have such a long process. We had to come up with the quickest possible solution to migrate the on-premises databases to the Azure DR server.  With the solution below, we were able to cut down the time to 5 hours by using an Azure Storage Account.

What is an Azure Storage Account?

An Azure Storage Account comes with the Azure subscription and is charged based on the consumption of storage. I won't be covering the features of Azure Storage Accounts, but I am going to show you the step-by-step procedure to use a storage account to take an on-premises SQL Server database backup and restore the database onto an Azure DR SQL server directly from the storage account.

1. Login to Azure portal with your login Id and password.

2. Go to Storage accounts and click on Create storage account (refer to screenshot below):

Azure Storage Account

3. Once you click create, you will see the below window. Enter an appropriate Storage account name and select a Location nearest you as it will impact the performance when you are trying to access the storage account to and from the SQL Server.

4. For Performance you can select standard, but if you are creating for production you should select premium. The Account kind should be BlobStorage as we are using it for backups only.

Create Azure Storage Account and select BlobStorage

5. Next you will have to choose the Replication type. Select Geo-redundant if your datacenter is scattered across the globe and you will be accessing the storage account from different regions. I choose Locally-redundant storage (LRS) for the demo.

Configure Replication for Storage Account

6. Choose Access tier as Hot, this will be a bit costly, but performance will be better for the backup and restore.

Azure Storage Access Tier

7. Keep the other options as the defaults. Click on Review and create.

Review configurations for the Azure Storage Account

8. It will take some time to create. Once the account is created you will be notified.

9. Once the storage account is created. Go to storage accounts and click on the storage account. Then click on Blobs.

Review Blobs in Azure Portal

10. You will have to create a storage container for the database backup. It's like a folder to save the backup. Click on Add container. Once a new container window opens as below, give the appropriate name and choose access level. If you don't want any unauthorized user to access it select private only. Click OK.

Configure Public Access Level in Azure Storage Account

11. The container will be created as below.

Azure Storage Container

12. Click on three dost at the end of the storage container and select container properties.

Container Properties for Azure Storage Account

13. You will see the URL for the storage account which you are going to use to take the backup and restore from (see below). Click on the copy icon to copy and save it somewhere such as notepad.

Database Backup Container Properties

14. Now go back to storage account and click on Access keys as show below and copy the access key as shown below. There are 2 access keys, you can use any one of them. It's nothing but a password to connect your storage account. Now, we are done with the storage account configuration for taking the backup.

Azure Storage Account Access Keys

15. Now, connect with SQL Server in Management Studio and click on security and then credentials and right click and click New Credential.

connect to your SQL Server in Management Studio and click on security and then credential and right click and click new

16. Enter credential name, you can give any name and it is not necessary to match the storage account name. The Identity name should be your storage account name and the password should be the access key you copied in the previous step. Copy the same key to confirm password and click OK.

Configure New Credential in SQL Server Management Studio

17.This will create a credential in SQL Server for your storage account which you will be using to take the backup to the storage account and will be used as an authentication. Currently, AD level authentication is not available and we need to use the storage account key to authenticate to the storage account.

Review the Credential setup in SQL Server Management Studio

18. Follow the same steps on your Azure DR server.

19. Now click on new query in SQL Server Management Studio and run this command. After it runs you will see the backup is completed. Connect to the Azure portal and verify the backup is in the storage container.

BACKUP DATABASE [AdventureWorks2014] TO  
URL = N'' 
WITH credential='databasebackup', FORMAT,  
NAME = N'AdventureWorks2014-Full Database Backup', 
Backup Database in SQL Server Management Studio with the credential

20. Now that the backup is complete, we will try to restore the backup from the storage account which we have taken in the previous step. Open a new query window and run the below query.

USE [master]


RESTORE DATABASE [AdventureWorks2014] from  
URL = N'' 
WITH credential='databasebackup', STATS = 10

USE [master]

Restore SQL Server Database in Management Studio with the Credential

As you can see above, I have restored the database on the same server for this demonstration, but note that in an actual scenario you don't want to run the restore command on the same server or else you will overwrite your production database. Make sure you run the restore command on your DR, Dev or UAT server.

If you are working on an Azure migration, then this tip will definitely save lot of time required for the backup and restore from on-premises to the cloud.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, April 15, 2024 - 2:38:57 AM - Alper Back To Top (92179)
I am having following error: `BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.` at step 19. How could I fix this error?

Tuesday, February 12, 2019 - 1:38:25 PM - annecao Back To Top (79014)

Thanks for the great article! 

One question is when you do the restore database at the later part of the article, are you  restoring to a Azure database or a datbase server on premise?


Tuesday, February 12, 2019 - 11:35:57 AM - Sajal Bagchi Back To Top (79013)

 Good Article. Just to give some updates, the above method takes the backup type as page blobs file. To further fasten up the backup/restore, we can stripe the backup file for large databases but that can only be done with block blob type (supported in sql2016 and above)

The credential too needs to be setup differently for block backup.

The name of credential should be the actual path or URL of the backup container and password would be shared access signature key

get free sql tips
agree to terms