How to Backup a SQL Server Database from On-Premises to Azure Storage

How to Backup a SQL Server Database from On-Premises to Azure Storage

How to Backup a SQL Server Database from On-Premises to Azure Storage

In a previous article, we discussed how you can create your first database on Azure SQL Database. In this article, we are going to see, how to backup a SQL Server database from on-premises to Azure blob storage. Moreover, we will see how we can restore a database on-premises, based on a backup file store on Azure blob storage.

 

High-level process to Backup a SQL Server Database from On-Premises to Azure Blob Storage

The high-level process to Backup a SQL Server Database from On-Premises to Azure blob storage is:

On Azure:

  1. If you haven’t created a generic storage account, you will need to create one (step 1).
  2. Check and take a note  (step 2).
  3. Create a private blob container (step 3).

 

On SQL Server/SSMS On-Premises:

  1. Create a credential object that can access the storage account (step 4).
  2. Run the BACKUP database T-SQL statement (step 5).
  3. Verify that the backup file is on Azure Storage (step 6).

* To restore the database from Azure blob storage to the on-premises SQL Server instance, you just run the relevant RESTORE command.

 

Step 1: Create a Generic Storage Account on Azure

The first step, is if you don’t have a generic Storage Account on Azure, to create one.

To do this, you navigate to “Storage Accounts” and click on the “Create storage account” button.

For the purposes of this demo, the account kind was set to “StorageV2”, that is “general purpose v2”.

How to Backup a SQL Server Database from On-Premises to Azure Storage

Then, there is a validation process. As you can see in the below screenshot, validation was successful.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

We then click on the “Create” button and after a few moments, we can see that the deployment was successfully completed.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

Step 2: Check Access Keys

The next step, is to check the access keys. Take a note of key1 or key2, since they will be used later on-premises while defining the credential to be used for accessing the Azure storage account.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

Step 3: Create Private Blog Container

The next step, is to create a private blob container. This container, will be used for storing the SQL Server database backup files.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

Great! Now let’s switch to the on-premises SQL Server instance and follow the below steps.

Step 4: Create a Credential Object On-Premises (via SSMS)

We need to create a credential object, that will be allowing access to the previously created azure storage and private blob container.

See the below screenshot for more information on how I did it.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

As you can see, in order to define the credential object, I used in the “SECRET” clause, the key provided in my Azure Storage account. Moreover, of course, I included the name of my Azure Storage account.

 

Step 5: Backup the On-Premises SQL Server Database to Azure (via SSMS)

Below, you can check out the sample database that I’m going to backup to Azure. The database is named “SampleDB” and it has a table named “tblTest” which has 3 records.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

Here’s the BACKUP T-SQL statement:

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

As you can see, it is the normal BACKUP statement, with the only difference that I am using the “URL” clause along with specifying the credential I created earlier.

Backup was successful! Now the backup file is stored on my Azure Storage, in the blob container.

 

Step 6: Verify that the Backup File is on the Azure Storage

To verify that the backup file is on the Azure Storage, you can navigate to the blob container via the Storage Explorer. As you can see, the backup file is visible.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

And here’s the properties of the backup file:

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

To Restore the Database from Azure Blob Storage to On-Premises

To restore the database from the Azure Blob Storage back to the on-premises SQL Server instance, you just use the RESTORE T-SQL statement along with using the “URL” clause and the credential object.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

 

Last, if run again the SELECT statement just like we did before the backup, we can see that our data is there and available for use.

How to Backup a SQL Server Database from On-Premises to Azure Storage - Article on SQLNetHub

Check the data after the restore operation.

 

Learn More

 

Check Also:

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out Artemakis’s eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© 2018 SQLNetHub

How to Backup a SQL Server Database from On-Premises to Azure Storage Click to Tweet
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

four × 4 =