By: Mohammed Moinudheen | Updated: 2019-12-02 | Comments | Related: > Azure
Problem
I have a number of SQL Server backups stored in Azure blob storage. Is there any way we can ensure that the backup files are not deleted permanently after deleting them from the blob storage container?
Solution
This tip assumes that you already have resources in your Azure environment and you are familiar with using the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200. You may have noticed in this tip, how we can configure the SQL Server maintenance plans to perform the database backups directly on to Azure blob storage. In that tip, you would have also noticed how the maintenance clean up activity can be performed by deleting the backup files that are older than a specific date.
In this tip, we will see a demo on how we can retrieve the backup files after the clean-up activity. That is, how we can retrieve the backups files that were deleted. The "Soft Delete" option is a critical feature that you would need to be aware of especially if you are using the blob storage for performing database backups.
Where do you find this "Soft Delete" option in Azure Blob Storage?
The soft delete is feature is only available in blob storage. You will view this option when you click on the storage account details on the Azure portal. Below is a screenshot where you can view this option on the left pane as shown below.
This is one of the features you see under the "Blob service" option.
Azure Blob Storage soft delete is turned off by default
Once you click on the "Soft delete" option, you will see this screen. You will see that this feature is off by default when you create new storage accounts.
From the description above, you can see that this feature can be used to recover blob data especially when the blobs or blob snapshots are deleted. It is just a matter of changing the "Soft delete" status to "Enabled" for this feature to take effect.
Here, you also came across a term known as "blob snapshot".
What is a blob snapshot?
A blob snapshot is a read-only backup of a blob taken at a specific point in time. Take for example, if you consider the fathersday.JPG image which is a block blob. You can create a new snapshot of that blob by clicking on the options as shown below.
Once you click on "Create snapshot", a new snapshot at a point in time is created.
You can click on the "View snapshots" option to list the snapshots that were taken. Here, in this screenshot you can see the snapshot that was taken with the timestamp of September 24, 10:15 pm.
The reason we discussed blob snapshots in this tip is because the soft delete option helps to recover blob data in case blob or blob snapshots are deleted.
Example of using Azure blob storage for performing SQL Server database backups
In this tip, you can see how you can use database maintenance plans to perform SQL database backups on Azure blob storage. However, we can use a simple backup command shown below for demo purposes. Refer the tip for details on how to create a storage account, create a credential and get details of the URL.
BACKUP DATABASE mo –- provide your DB name TO URL = 'https://dbdummy.blob.core.windows.net/onpremisebackup/mo.bak' –- give the correct URL WITH CREDENTIAL = 'sqlcred'; -- enter the credential GO
Once the backup command completes successfully, you will be able to view the backup file in Azure storage.
From the screenshot, you can see that the storage account is "dbdummy". When you check the "Soft delete" option for this storage account, you will notice that it is disabled by default as mentioned earlier. Now, for the purpose of this demo, just delete the .bak file that was performed on the blob storage. For deleting, click the ellipsis on the right-hand corner and select the "delete" option.
Once you click on "Delete", you will see this pop-up.
As we didn’t perform any blob snapshot, just click on "OK". Once done, you will see confirmation of the deletion.
You will notice this option – "Show deleted blobs" in the storage container. When you click on it, the storage container will still be empty as the "soft delete" option was disabled for the storage account.
This means that you are unable to recover those SQL Server database backups you performed. Once it is deleted from the storage container, it is gone and in case you require the backups for restore purposes, you would be in unnecessary trouble.
Demo after enabling the Azure Blob Storage Soft Delete option
In this section, let us enable the "soft delete" option. As shown earlier in the tip, click on the "soft delete" option under blob service and select the "Enabled" option as shown.
When you click on the information icon, you can see information regarding the retention period for the soft deleted data. The default retention period appears to be 7 days. Click on the "Save" option after enabling the "soft delete" option and selecting a suitable retention period. Once done, you will see this confirmation.
Backup SQL Server database to Azure storage for purpose of demo
In order to test the "soft delete" option after it is enabled, perform the step again to back up SQL server database to Azure storage. You can re-use the script used previously to back the database to URL.
BACKUP DATABASE mo – - provide your DB name TO URL = 'https://dbdummy.blob.core.windows.net/onpremisebackup/mo_after_enabling_soft_delete.bak' – - give the correct URL WITH CREDENTIAL = 'sqlcred'; -- enter the credential GO
Once backup completes successfully, it will be available on Azure storage.
In the next step, delete this newly created backup as previously shown. Once the backup is deleted, you will see that it is no longer present in the storage container.
However, you will notice that the "Show deleted blobs" option comes to your rescue. Once you enable that option, you will be able to view the deleted backup file.
You can view properties of this deleted blob file by clicking on the ellipsis option on the right-hand corner.
On clicking the "Blob properties" option, you will be able to view further details about this backup file. Below is a screenshot of the blob properties.
Some of the useful information from the blob properties are highlighted above. One thing of importance is the "DAYS UNTIL PERMANENT DELETE" option that shows the remaining number of days this backup file will be still available before it gets permanently deleted. This setting is visible at the time you enable the "soft delete" option on the storage account. You could extend the retention for up to 365 days.
Restore the deleted backup file from Azure Blob Storage
Restoring the deleted backup file is straightforward. You only need to click on the option to "Undelete" as shown which will restore the backup file that was deleted.
Once you click on "Undelete", you will be able to view the backup file that was deleted.
In this demo, you saw how easily you could restore a deleted file when the "soft delete" option is enabled.
What happens if we delete the storage account or storage container even though the "soft delete" option is enabled?
The "soft delete" option will have no impact as all the objects will get permanently deleted in this case. In this example, we will delete the storage account where the "soft delete" option is enabled.
Once you click on the "Delete" option, you will see this screen.
On clicking the "Delete" option, you will see that the storage account is permanently deleted and there is no way you can recover any of the blob objects even though "soft delete" was enabled.
In order to overcome this type of issue, you can use the lock resources option in Azure. You can refer this tip on locking resources for further reading.
Next Steps
- In this tip, you learned about the use of the "soft delete" option in Azure
- In the demo, you saw what happens to blob objects when the "soft delete" option is disabled
- In the demo, you saw what happens to blob objects when the "soft delete" option is enabled
- You also learned about blob snapshots
- In the demo, you saw how you can restore a deleted object after it was deleted
- In the demo, you saw what happens when either the storage account or container is deleted
- Try out this demo on your own Azure environment
- Refer to these other articles available on MSSQLTIPS related to Azure
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-12-02
