Writen by Kristy Noms, in 07/06/2022
4 minutes of reading
Backing up on-premises SQL Server Databases to Azure Blob Storage
Storing your backups remotely with Azure using a storage account.
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.
2 – Backup Options
We have two types of blobs when backing up a database to URL: Page blobs and Block blobs:
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.
Now you should be able to create a routine to keep your backups safe in Azure or even create occasional backups.
it`ll be easier to migrate to azure if you plan to, since the backups are already there.
Don`t forget that It’s also important to create a retention plan for these files.