In a previous post, I described how you can use Azure storage to store SQL Server database backup files. This flexible option offers the ability to get your critical backups off site and in certain regions, they could be configured to be automatically redundant to a secondary region. Having backups of your backups is a backup plan that I would support. Regardless of how many copies of the backups you have, you still need to be able to restore them. Restoring them routinely helps to validate that the backups are good and safe as well as ensure you have the process in place in the event you need to restore.
So how do you restore from Azure storage? You do so from an URL. Let’s take a look!
When you backup a database to Azure, there are two types of blobs that can be utilized, namely page and block blobs. Due to price and flexibly, it is recommended to use block blobs. However, depending on which type you used to perform the backup will dictate how the restores are performed. Both methods require the use a credential, so that information will need to be known before being able to restore from Azure.
Page Blobs
If you used page blobs, the credential was created using a pre-configure key. Unlike other keys, this one doesn’t have an expiration date assigned to it so as long as the keys have not been rotated, you don’t need to do anything else. All you have to do is issue a simple RESTORE command and the database will be restored. Note that you do have to utilize the “WITH CREDENTIAL” switch in the command and specify which credential to use.
RESTORE DATABASE Scratch2 FROM URL = 'https://backupsdemo.blob.core.windows.net/sqlbackups/Scratch2_pageblob.bak' WITH CREDENTIAL = 'PageBlobs', RECOVERY, STATS = 5
If all goes to plan, you’ll see the normal status messages as shown below:
Block Blobs
Block blobs can be a little more complicated. A backup to Azure utilizing block blobs uses a Shared Access Signature (SAS) to facilitate security. This signature is controlled through the Azure portal (or via various scripting languages such as Powershell) and is required in order to backup and restore from the blob. Some things to note with them:
- They have an expiration date/time. If you backed up using SAS and did not specify an expiration date further into the future, the SAS can and will expire. This will force you to generate a new signature and subsequently update the credential you have on your SQL Server.
- They need an IP address or range to accept requests from.
If the signature that was utilized to backup the database expired, you will need to regenerate one. Once the signature is regenerated, you will need to take the SAS token and update the password of the credential. Remember that the credential, in the case of using SAS, will be named the same as the blob endpoint along with the container label.
Here is where the token needs to go:
Once the credential has been updated, you can then begin to restore the database with, again, your standard RESTORE DATABASE command.
RESTORE database Scratch2 FROM URL = 'https://backupsdemo.blob.core.windows.net/sqlbackups/Scratch2_Restore_blockblob.bak' WITH replace, RECOVERY
As with page blobs, if all is well you will see the standard messages when doing a restore:
Summary
Just like backing up to URL is fairly easy to accomplish, restoring from URL is just as easy. There aren’t any serious moving parts to make this happen. Storing your backups in Azure is a great way to get your backups offsite and quite possibly even out of the region. If you are looking for a way to get your backups off site, make sure to look at Azure along with any other providers. I’m a huge fan.
Enjoy!
© 2019, John Morehouse. All rights reserved.
