Azure Soft Delete Option for Blob Storage to Recover Deleted Files


By:   |   Updated: 2019-12-02   |   Comments   |   Related: More > 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.

azure blob storage soft delete

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.

azure blob storage soft delete

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.

create blob snapshot

Once you click on "Create snapshot", a new snapshot at a point in time is created.

create blob snapshot

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.

file snapshots azure blob storage

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.

blob storage files

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.

delete file in azure blob storage

Once you click on "Delete", you will see this pop-up.

delete blobs confirmation

As we didn’t perform any blob snapshot, just click on "OK". Once done, you will see confirmation of the deletion.

delete file in blob storage

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.

show deleted files in azure blob storage

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.

enable soft delete for azure blob storage

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.

blob storage files

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.

show deleted files

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.

show deleted files

You can view properties of this deleted blob file by clicking on the ellipsis option on the right-hand corner.

azure blob storage blob properties

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.

blob storage deleted file 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.

undelete blob storage file

Once you click on "Undelete", you will be able to view the backup file that was deleted.

undelete blob storage file

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.

delete storage account

Once you click on the "Delete" option, you will see this screen.

delete storage account

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.

successfully deleted storage account message

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


Last Updated: 2019-12-02


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools