Lesson 9: Manage backup sets and file-snapshot backups
In this lesson, you will delete a backup set using the sp_delete_backup (Transact-SQL) system stored procedure. This system stored procedure deletes the backup file and the file snapshot on each database file associated with this backup set.
If you attempt to delete a backup set by simply deleting the backup file from the Azure blob container, you will only delete the backup file itself - the associated file snapshots will remain. If you find yourself in this scenario, use the sys.fn_db_backup_file_snapshots (Transact-SQL) system function to identify the URL of the orphaned file snapshots and use the sp_delete_backup_file_snapshot (Transact-SQL) system stored procedure to delete each orphaned file snapshot. For more information, see File-Snapshot Backups for Database Files in Azure.
To delete a file-snapshot backup set, follow these steps:
Connect to SQL Server Management Studio.
Open a new query window and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine (or to any SQL Server 2016 instance with permissions to read and write on this container).
Copy and paste the following Transact-SQL script into the query window. Select the log backup you wish to delete along with its associated file snapshots. Modify the URL appropriately for your storage account name and the container that you specified in Lesson 1, provide the log backup file name and then execute this script.
In Object Explorer, connect to Azure storage.
Expand Containers, expand the container that your created in Lesson 1 and verify that the backup file you used in step 3 no longer appears in this container (refresh the node as necessary).
Copy, paste and execute the following Transact-SQL script into the query window to verify that two file snapshots have been deleted.
-- verify that two file snapshots have been removed SELECT * from sys.fn_db_backup_file_snapshots ('AdventureWorks2014');
End of Tutorial