SQL Backup to URL (Azure Storage account)

How to backup up a copy of an on premise database, or Azure SQL Server database to an Azure Storage account.

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.

Backup Steps

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

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, click Add
  3. Create Storage container – 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>

https://i1.wp.com/dataplatformbootcamp.azurewebsites.net/wp-content/uploads/2017/03/lab1b-1.png

  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 – Blob
    3. Click Create

https://i2.wp.com/dataplatformbootcamp.azurewebsites.net/wp-content/uploads/2017/03/lab1b-2.png

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!).

https://i2.wp.com/dataplatformbootcamp.azurewebsites.net/wp-content/uploads/2017/03/lab1b-3.png

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

  1. 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.

https://i1.wp.com/dataplatformbootcamp.azurewebsites.net/wp-content/uploads/2017/03/lab1b-4.png

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.

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

Using storage account identity and access key

Notes: T-SQL Sample, <mycredentialname> = credential name used internally, <mystorageaccountname> = name of the storage account, <mystorageaccountaccesskey> = key 1 captured in previous task.

IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = '<mycredentialname>') 
CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>' ,
SECRET = '<mystorageaccountaccesskey>'; 

IF NOT EXISTS 
(SELECT * FROM sys.credentials WHERE name = '<mycredentialname>') 

CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>' 
,SECRET = '<mystorageaccountaccesskey>'; 

Now, you will translate this sample T-SQL into a credential for your Backup!

Using Notepad to build your T-SQL statement – Substitute the bracketed areas next with your account information.

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

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

IDENTITY = ‘YourAccountName‘ (Note: from Create an Azure Storage account – 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

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

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

GO 

Note: You will need to substitute YourAccountName and backup

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

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

 

With the storage account name and blob name. Substitute the credential name if necessary.

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

Click New Query.

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

Then click Execute.

The command should show Completed Successfully.

Advertisements
This entry was posted in Azure SQL Server, 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s