Summary : The subjected issue occurred on while taking SQL Server Database backup on Windows Azure Storage.
The issue is very common , this normally comes when the URL back fails to complete, while the backup files is created on azure storage (backup failure do not drop the file @the storage).Now when the user tries to delete the backup(blob) files using storage portal or Azure Storage Explore it pop’s up the below message.
Error deleting blob ‘AdventureWorks20121_FULL.bak’
Back to progress operations
There is currently a lease on the blob and no lease ID was specified in the request. RequestId:ba9c4274-0001-00c7-0351-516134000000 Time:2015-03-05T16:31:31.8959787Z
T-SQL Backup failure error
Msg 3202, Level 16, State 1, Line 14
Write on “https://dbproxazurestorage.blob.core.windows.net/containerstorage/AdventureWorks20121_FULL.bak” failed: Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (500) Internal Server Error.
Msg 3271, Level 16, State 1, Line 14
A nonrecoverable I/O error occurred on file “https://dbproxazurestorage.blob.core.windows.net/containerstorage/AdventureWorks20121_FULL.bak:” Error could not be gathered from Remote Endpoint.
Msg 3013, Level 16, State 1, Line 14
BACKUP DATABASE is terminating abnormally.
Investigation / Troubleshooting
When backing up to or restoring from Windows Azure storage, SQL Server acquires an infinite lease in order to lock exclusive access to the blob. When the backup or restore process is successfully completed, the lease is released. If a backup or restore fails, the backup process attempts to clean up any invalid blob. However, if the backup fails due to prolonged or sustained network connectivity failure, the backup process may not be able gain access to the blob and the blob may remain orphaned. This means that the blob cannot be written to or deleted until the lease is released.
I have to admit that Azure portal is not friendly enough to provide the exact issue or error.Though it give little information but that is not enough to resolve the issue.To get to the root of the issue download Azure Storage Explore and connect to you Azure Storage.
select the backup file and click view from the tool bar. It will popup the property page of the backup.In this you will see the lease duration is set to Infinite and Lease status is locked (Highlighted in Red).
To delete the files the lease has to be unlocked.This is was a real pain , after spending quite some time and couple of searches i came across this informative Microsoft article. The article has a power shell script to unlock the lease of the blob files. you can find the script below.
If there are blobs with locked leases, you should see the following messages:
The lease on <URL of the Blob> is a restore lease: You will see this message only if you have a blob with a restore lease that is still active.
The lease on <URL of the Blob> is not a restore lease Breaking lease on <URL of the Bob>.
After the lease is broken , you can easily delete the files from portal or Azure Storage Explorer.
[string]$storageKey=’Provide your storage key’
[string]$storageAssemblyPath=’C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinnMicrosoft.WindowsAzure.Storage.dll’
# Well known Restore Lease ID
$restoreLeaseId = “8D2256EAD49C35D”
# Load the storage assembly without locking the file for the duration of the PowerShell session
$bytes = [System.IO.File]::ReadAllBytes($storageAssemblyPath)
$cred = New-Object ‘Microsoft.WindowsAzure.Storage.Auth.StorageCredentials’ $storageAccount, $storageKey
$client = New-Object ‘Microsoft.WindowsAzure.Storage.Blob.CloudBlobClient’ “https://$storageAccount.blob.core.windows.net”, $cred
$container = $client.GetContainerReference($blobContainer)
#list all the blobs
$allBlobs = $container.ListBlobs()
$lockedBlobs = @()
# filter blobs that are have Lease Status as “locked”
foreach($blob in $allBlobs)
$blobProperties = $blob.Properties
if($blobProperties.LeaseStatus -eq “Locked”)
$lockedBlobs += $blob
if ($lockedBlobs.Count -eq 0)
Write-Host ” There are no blobs with locked lease status”
if($lockedBlobs.Count -gt 0)
write-host “Breaking leases”
foreach($blob in $lockedBlobs )
$blob.AcquireLease($null, $restoreLeaseId, $null, $null, $null)
Write-Host “The lease on $($blob.Uri) is a restore lease”
if($_.Exception.RequestInformation.HttpStatusCode -eq 409)
Write-Host “The lease on $($blob.Uri) is not a restore lease”
Write-Host “Breaking lease on $($blob.Uri)”
$blob.BreakLease($(New-TimeSpan), $null, $null, $null) | Out-Null