Backing up on-premises SQL Server Databases to Azure Blob Storage

Here in this lab I`ll teach you how to store your backups in Azure, this could be your first step into the cloud, and doing this you`ll have a safe copy of your databases.

Let`s go through the necessary steps:

1 – Storage Account

First of all, we need to create a storage account, I provisioned one named kristysqlbackups with public access enabled so we can try without further configuration.

Container created:

2 – Backup Options

We have two types of blobs when backing up a database to URL: Page blobs and Block blobs:

Fonte: SQL Server Backup to URL – a cheat sheet

In older versions ( SQL 2012-2014) we have only page blob available, in this case large databases over 1 TB can’t be backed up to blob storage.

3 – SQL Server credentials

SQL Server 2012 – 2014

In this case we’re using the storage account name and an access key to authenticate: 

SQL Server 2016 – 2019

For this credential we’re gonna use a SAS Token that allows us to authenticate clients without using the storage account name, and to take advantage of the block blobs, it’s the only type of authentication accepted.

Create screen from SAS:

Now that we got our SAS token we can create our credential in SQL Server:

The name must be the URL from the container, identity will be ‘SHARED ACCESS SIGNATURE’ and the secret must not have the question mark:

4 – Backup statement

SQL 2012 – 2014

We need to pass our credential and can only backup to one file limited to 1 TB.

SQL 2016 – 2019

We can split our backup into multiple files for better performance and we don’t need to pass our credential.