This article discusses the concept of SQL Server backup and the various components required to use the Microsoft Azure Blob storage service as a target for the backup. As we all know, disk and tapes were the default destinations of choice until the evolution of the cloud platform. In addition to this, now we can expand the native functionality of SQL Server backup to cloud storage; the Windows Azure Blob storage. This functionality was added first in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences.

SQL Server database Backup to Azure Blob Storage is a process designed to perform almost like a backup device, such as disk or tape. During the backup or restore process, a URL is selected as a “device type” which in turn triggers a VDI (Virtual Backup Device Interface) client process. The process acts as an intermediary agent to send the database backup to the Azure Blob Storage.

In this article we touch base on the following topics:

1. Creating an Azure Storage account
2. Configuring Storage Container
3. Creating a credential for authorization using SSMS and T-SQL
4. Using the GUI to Perform a database backup and restore
5. Using a T-SQL script to backup and restore the database
6. Implementing the backup-and-restore functionality using PowerShell
7. And more…

Before we perform the database backup, it’s important to understand the Blob Storage Service and SQL Server key components, and their concepts. To start, let’s do a little prep work to setup Microsoft Azure Create an Azure Storage account. In the first step, we create a storage account at the Azure Portal. Using the Blob Storage Service, we configure the Azure Blob Storage Container.

Next, let’s review the SQL Server components.

• The URL is a unique identifier used for every backup file.
• A SQL Server credential is used for authenticating the resources. The backup and restore process must use this credential to authenticate the blob storage service and its associated containers.
• In the last step, perform the backup to the cloud storage. The steps are almost the same as all the other backups, albeit with a few more configuration options.

Step-by-step instructions to configure Azure Blob Storage

1. First, Login to the Azure Portal
2. Locate Storage accounts on the left pane, click Create storage accounts
   1. This section deals with creating the storage account to be able to opt for the required model which has a direct impact on the cost based on usage.
   2. Name the account; in our example we’ll use sqlshackdemo. The next step is to assign this storage account to a resource group. We have two options here; either use an existing resource group or create a new one. Let’s create a new one for the demonstration and name it as sqlshack.
   3. Next, choose the location, depending on where you want to host the storage. (I’ve opted for US-East.) Click on the Create button. Wait a minute or two for the storage account to be created.
      The following information is required to create a storage account:
      1. Name: sqlshackdemo; a unique name, all lowercase letters
      2. In this case, the default setting is used for the Account Kind
      3. Default Performance setting – Standard
      4. Default setting – Storage service encryption – Enabled
      5. Resource group – Create new resource group named sqlshack
      6. Select Location – East-US is selected
      7. Check the Pin to dashboard option
      8. Click on the Create button
   4. Once the storage account is created, we’ll need to provision the blob storage. To do this,
      1. browse the storage account, in the Overview blade, select the option to create a blob storage.
      2. Click on Blobs. It would open up a new window.
      3. Click the + container icon to create a new container.
      4. Name the container; I chose sqlshackbackup. Remember that the name has to be all lowercase letters.
      5. Select Private as the Access type
      6. and then click on the Create button.

Now, we can go over to SQL Server and start working on creating the credentials that will be used to authenticate into the Azure account. In the New query window, use CREATE CREDENTIAL to create credential.

The Identity property is the name of the storage account. In this case, it is sqlshackdemo.

The Access key is the secret key of the storage account. To find the access key, click on Settings and select Access keys in the Storage account blade. To the right, you should fine key1, which is the access key, select the Copy to clipboard icon. Save this key information somewhere safe. The syntax to create to credential is as follows:

Now, Prepare the T-SQL to backup the database to the URL using blob storage account service and the access key. The following example initiates a full database backup of the SQLShackDemoATC database to the blob storage (URL+Storage+Container+filename).

Validate the output by browsing the blob storage. We can see that the SQLShackDemoATC.bak file exists at the storage. We can browse the URL of the file and this pointer is used in the upcoming section to restore the database.

For the demo, I will be restoring the SQLShackDemoATC with a different name SQLShackDemoATC_Clone to the same SQL instance.

The following output shows that the database was restored and with the same number of tables as the source database. This proves that the restoration of the database was successful.

Perform Database backup task using SSMS (SQL Server Management Studio)

1. In the Object Explorer, browse the Databases, right-click on the database, go to Tasks, and then click Back Up…

   

2. On the General page, go to the Destination section. At the Back up to: drop-down list, select URL, and then click Add. This will open the Select Backup Destination dialog box

   

3. Click on New container. This will open a Connect to Microsoft Subscription page.

   

4. At the Select Storage Account drop down, select the storage account. In this case, it’s sqlshackdemo.

   

5. In a similar way go to Select Blob Container, and choose sqlshackbackupdemo

   

6. Click on OK.

7. Now, you’re in the Back Up Database wizard window. At the destination field, we can see the URL of the backup file at the storage.

   

8. The next screen shows the progress of the backup.

   

9. The last screen displays a message stating that the backup was successful.

   

We can see that the backup file was written to the blob storage. This shows us that the backup can also be taken using SSMS.

Next, this lets us demonstrate the steps to perform database backup to URL using PowerShell.

This section provides the PowerShell scripts to automate the database backup using the available PowerShell cmdlets.

1. Backup-SqlDatabase—used to back-up the database
2. Restore-SqlDatabase—used to restore the database backup
3. New-SqlCredential—used to create the SQL credentials to authenticate at the Azure Blob Storage
4. Get-SqlCredential—used to fetch the existing credentials

Summary

The three different ways to back-up the database to URL have been explained with examples using SSMS, T-SQL and PowerShell.

Points to remember

1. It is recommended to use unique file names for every backup in order to prevent accidental overwriting of the blobs.
2. During the creation of the container, it is recommended that you set the access level to private so that only the users or accounts that can provide the required credentials can read or write to the blobs in the container.
3. For SQL Server databases on an instance of SQL Server running in a Windows Azure Virtual Machine, use a storage account in the same region as the virtual machine itself in order to avoid the costs incurred in transferring data between regions. Using the same region also ensures optimal performance of the backup and restore operations.
4. Backup failurs can result in an invalid backup file. We recommend periodic identification of failed backups and deleting the blob files. For more information, see Deleting Backup Blob Files with Active Leases.
5. Using the WITH COMPRESSION option during backup can minimize your storage costs and storage transaction costs. It can also decrease the time taken to complete the backup process.

Table of contents

References

• Use PowerShell to Backup Multiple Databases to Windows Azure Blob Storage Service
• Back up Multiple Databases to Azure Blob Storage – PowerShell
• SQL Server Backup to URL

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021