SQL Server has given us the option to backup our databases directly to Azure BLOB storage for a while now but it’s not something that I’ve had all that much call to use until recently.
So this is just going to be a quick walk through on how you can backup your on premise SQL Servers to Azure BLOB storage. I’m going to assume that you’ve already got an Azure account, if you haven’t, you get set up a free trial which will see you good for this demo.
Creating a Container
Setting up a Storage Account
The first thing that you’re going to need to do is create a container that you can backup to.
From the menu on the left of the Azure portal, select ‘Storage Accounts’
From there, you’re going to want to add a storage account
now you can go ahead and setup your storage account
| Subscription | The name of your subscription, (Free Trial if, like me you’re using a free trial). |
| Resource Group | If you’ve not got any resource groups setup, click ‘create new’ and pop a name in for a new one. |
| Storage Account Name | The name for your storage account, make this something that’s going to make sense down the line. |
| Location | Where do you want your data stored? |
| Performance | Stick with standard if this is for testing, I’ve not compared the two for performance. |
| Account Kind | Just stick with the default of StorageV2 here. |
| Replicaiton | What sort of resilience do you want? LRS is the cheapest so that’s what I’ve gone for here. If this was in production I might want to look at other options. |
I’ll stick with the rest of the defaults for the time being and hit ‘Review + Create’. This will do a quick check over of things and if it’s happy, give you the option to create your storage account.
Creating a Container
Now we’ve got our storage account, click on ‘Storage Accounts’ again in the left hand ribbon and we’ll now see our new storage account.
Click on the storage account link and you’ll see some information about your new account. From here you can also create a container. Click on ‘Containers’ either on the main screen or find it in the left hand ribbon.
You’ll now have the option to add a container
now give your container a name and hit create…
Create a Shared Access Signature (SAS)
Now to finish off the Azure part of this, we need to create a shared access signature (SAS).
Head back to the main screen for the storage account and select ‘Shared access signature’ from the lift hand ribbon.
Under allowed services you’re going to need to make sure that ‘Blob’ is selected and ‘Container’ for allowed resource type. Also notice that there’s a start and expiry date and time for the signature. You can also restrict the ip addresses that can access your backups from here.
When you’re done, hit ‘Generate SAS and connection string’
As soon as you’ve done that, you’ll be given the SAS token. That’s what we’re going to need when we come to access it from SQL. Copy that off, but drop the leading ? when you do, we don’t need that.
Create a SQL Credential
We’re now going to create a credential to allow us to connect to our Azure container.
USE master GO CREATE CREDENTIAL [https://<storage account name>.blob.core.windows.net/<container name>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<SAS token>'
so in my case that’s going to look like
USE master GO CREATE CREDENTIAL [https://sqlundercoverbackuptest.blob.core.windows.net/backups] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-10-10&ss=b&srt=c&sp=rwdlacx&se=2021-06-18T22:46:13Z&st=2020-06-18T14:46:13Z&spr=https&sig=QUBl6ZaSYc5YKwGDZIYpExhYYjh3B%2FHgmRMD3w%3D'
IMPORTANT – Remember to lose the ? from that start of the SAS token, it should start with sv=
Backup Database
Now to backup your database, the command is virtually the same as if you’re backing up locally except you need to specify ‘TO URL’
BACKUP DATABASE [SQLUndercover] TO URL = 'https://sqlundercoverbackuptest.blob.core.windows.net/backups/SQLUndercover.bak'
And it’s as easy as that, you should now be able to backup your on-prem SQL Servers to Azure BLOB storage.
Backup Retention
You can easily also setup a retention period for your backup files.
To do this, head back to the main screen for your storage account and look for ‘Lifecycle Management’ in that left ribbon. It comes under the Blob service section in case you’re having trouble finding it.
From there you can create a new rule to control the life cycle of your backup files from moving them to cool storage, archive or deleting them.
In the example below, I’m going to move my files to cool storage after 5 days and then get shot of them after 30 days.
By default, this rule will apply to all containers for this storage account. If you want apply it only to certain containers, you can set filters in the Filter set screen.
Thanks for reading and I hope you’ve found this useful.