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.
Now click to add a new account
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.
Once this the storage account has been created you’ll be able to see this account in the storage account area
To open the storage account click on the name of the storage, in this example datadoorstore and you’ll be presented with the below
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.
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)
Once the container has been created we need to make a note of the URL for the container
Now that’s us completed with the Azure console, we can now move to SSMS.
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
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.