Backup SQL Server to an Azure Storage Account

One of the benefits of backing up SQL Server to Azure, is an immediate “off site” storage solution. Another, is that Azure storage account can be geo-replicated for additional backup copies.

There are a few ways to make this work, here are the details I was able to work out for a “hands on” lab.

For this lab, you will backup up a copy of an on premise SQL Server database to an Azure Storage account.

LAB Notes: Any names that need to be added at time of creation are indicated with <>, such as <sqlstorageaccountname> used in the next example are suggestions only and will not include the <> symbols! You are encouraged to develop your own naming conventions. Screen captures shown are representative examples of each task.

Steps involved:

  1. Create an Azure Storage account
  2. Create a Credential
  3. Perform a full database backup to a URL

Create an Azure Storage account

In order to backup a SQL Server database to Azure, you will need to first create an Azure Storage account.

  1. The steps to backup a database to a URL are as follows:
    Login the Azure Portal https://portal.azure.com
  2. Locate Storage account, click Create
  3. Create Storage Account – Enter the following information:
    1. Name – <sqlstorageaccountname>Note: this must be globally unique!
    2. Account Kind – General Purpose
    3. Performance – Standard
    4. Storage service encryption – Enabled
    5. Resource group
      1. Create new
      2. <sqlbootcamp>

      Lab1-img1

 

  1. Within storage container (created last step)
  2. On the container properties page, under Blob Services, click Containers.
  3. To Create a container for the SQL backups, Click (+ Container)
    1. Name: <sqlbackup>
    2. Access type – Private (default)
    3. Click OK

Lab1b-2a

Select the newly created container (last step), then Properties. Locate the URL line on right and copy this information into the clipboard.

Note: Pasting this information into Notepad is a convenient way to store this information for a subsequent lab (hint!).

Lab1-img3

It should appear similar to this (substituting storage container and container name):
https://<sqlstorageaccountname >.blob.core.windows.net/<sqlbackup>

Next, obtain the access key for this storage account. Select the (root) storage container name, then under SETTINGS, select Access keys. To the right of key1, select the copy to clipboard icon. Save this key to notepad (paste) – it will be used in a future step.

SNAGHTML1949f7b1

 

Creating a Credential

You will now create a Credential with SQL Server, which will allow access to the Azure storage account from SSMS. This Credential is stored locally within SQL Server.

Using storage account identity and access key

Take a moment and review the following example – SQL Server credential is created to allow authentication to the Microsoft Azure Blob storage service.

CREATE CREDENTIAL [backup] WITH IDENTITY = '<YourAccountName>'  
,SECRET = '<YourStorageAccessKey>';  
GO

Tip: Use Notepad to build your T-SQL statement – Substitute the following <bracketed> areas next with your account information.

CREDENTIAL [backup]   Note: Whatever credential name you use here, you will need to specify for the following task! “Perform a full database backup to a URL”

IDENTITY =YourAccountName‘   Note: from Create an Azure Storage accountStep 3 A just the first name, not the complete FQDN

SECRET =YourStorageAccessKey‘   Note: from Create an Azure Storage accountobtain storage access key – last step

Then, copy the entire statement into the clipboard.

Open SQL Server Management Studio (SSMS) and connect to your local copy of SQL Server.

Click New Query.

Copy and paste the above T-SQL into the new query window.

Then click Execute.

The command should show Completed Successfully.

Perform a full database backup to URL

The following example perform a full database backup of the AdventureWorks2016 database to the Microsoft Azure Blob storage service. Take a moment and review the syntax.

Backup To URL using storage account identity and access key

BACKUP DATABASE AdventureWorks2014 
TO URL = 'https://<YourAccountName>.blob.core.windows.net/<sqlbackup>/AdventureWorks2014.bak' WITH CREDENTIAL = '<backup>', COMPRESSION, STATS = 5;

Note: Using NOTEPAD, you will need to substitute YourAccountName and sqlbackup with the storage account name and blob name. Substitute the credential name backup used in the preceding step. Be sure to copy the entire SQL statement!

Open SQL Server Management Studio (SSMS) and connect to your local copy of SQL Server.

Click New Query.

From NOTEPAD – Copy and paste the T-SQL directly into the new query window.

Then click Execute.

The command should show Completed Successfully.

The following sample OUTPUT is provided:

Output
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
Processed 24288 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
100 percent processed.
Processed 2 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24290 pages in 91.076 seconds (2.083 MB/sec).
Advertisements
This entry was posted in Azure, Azure SQL Server, SCCM, SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.