Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Updated: 2019-02-12   |   Comments (2)   |   Related: More > Azure

Problem

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?

Solution

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'https://databasebackup.blob.core.windows.net/databasebackup/adventureworks2014_backup_2018_12_07_092414.bak' 
WITH credential='databasebackup', FORMAT,  
NAME = N'AdventureWorks2014-Full Database Backup', 
NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO			
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]
GO

ALTER DATABASE [AdventureWorks2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [AdventureWorks2014] from  
URL = N'https://databasebackup.blob.core.windows.net/databasebackup/adventureworks2014_backup_2018_12_07_092414.bak' 
WITH credential='databasebackup', STATS = 10
GO

USE [master]
GO

ALTER DATABASE [AdventureWorks2014] SET MULTI_USER
GO			
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


Last Updated: 2019-02-12


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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?

Thanks


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

 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


Learn more about SQL Server tools