SQL Server Backups to Azure Storage

Since SQL Server 2014 we’ve had the capability to  backup databases directly onto azure storage, before you’re able to do this however there are a few steps which need to be setup first via Azure and in SQL Server itself.

CREATE STORAGE ACCOUNT, KEYS AND CONTAINER

Firstly, logon to your Azure account, if you don’t have an azure subscription I’d recommend signing up to Visual studio dev essentials, you get a  monthly amount of credits for azure and also pluralsight (LINK)

Once you’re into the Azure management console we need to firstly create and storage account, so to start click on the storage accounts icon on the left hand pane.

1

Now click to add a new account

2

And fill out the create storage account form, as below, remember the name needs to be unique and lowercase, in this example I’m naming it datadoorstore but you can choose our own naming.

3

Once this the storage account has been created you’ll be able to see this account in the storage account area

4

To open the storage account click on the name of the storage, in this example datadoorstore and you’ll be presented with the below

5

Now  we need to select the Access keys selection from the left side pane to open the below screen, once in here we need to make a note of the Storage account name and Key1 as we’ll be using these later.

6

Now we need to create a container for the backups, so again in the left pane click on the Containers selection and click to add container and filling the details as below (again you can use your own naming)

7

Once the container has been created we need to make a note of the URL for the container

8

Now that’s us completed with the Azure console, we can now move to SSMS.

 

CREATE CREDENTIALS

This is quite simple to carry out, you can use the GUI’d but I prefer using T-SQL (Always recommended)

CREATE CREDENTIAL myAzureCredential 
WITH IDENTITY= 'StorageAccountName' -- This is the name of the storage account you created above 
, SECRET = 'StorageAccessKey' --This is either key 1 or 2 which you made a note of earlier

Once this is completed we’re able to move onto the backing up and restoring part.

 

BACKUP DATABASE TO AZURE STORAGE

For this example I’ve created an empty data called AzureBackupDB

Now we simply execute the below T-SQL command to backup the database

BACKUP DATABASE AzureBackupDB
TO URL = 'https://datadoorstore.blob.core.windows.net/datadoorcont/AzurebackupDB.bak'
WITH CREDENTIAL = 'myAzureCredential'
GO

The URL is the one from the container we made a note of and the credential is the one we created in the last step.

Now if we return to the container screen in the Azure Console and refresh the screen you’ll see your backup file like below

9

Hope this helps you get started with backing up to Azure, even if you don’t want to store all of your backups to the cloud then its still useful to know if you need to backup a database and there isn’t any space locally happen this could be a temporary solution.

Advertisements

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