Solving Errors with Existing BLOB Leases When Using BACKUP TO URL

BACKUP TO URL was introduced as an add-on in Cumulative Update 2 for SQL Server 2012 Service Pack 1 and as a built-in feature for SQL Server 2014. I previously described this in a blog post.

We have been using this in a variety of ways from on-premises systems:

For example, it is an easy way to distribute a backup of a database to a large number of systems. Imagine you have a chain of retail stores that needs product and other reference information updated regularly. You can keep this data in a separate database at the head office, back it up to an Azure Storage account, and have each store download it separately.  This has major bandwidth and reliability improvements over other solutions such as having each store maintain a VPN connection to the head office.

As another example, we have clients who simply aren’t able to gain enough space on their SANs to keep enough local copies of their backups.

The more common scenario though is to use it for backups from Azure Virtual Machines that are running SQL Server. Rather than performing a backup to a virtual machine virtual disk, we achieve better performance by bypassing the file-system on the virtual disk and backing up directly to a URL. Both end up in Azure storage but backing up directly to storage gives us both better performance, and can help to avoid the drive limit and size restrictions for virtual machines.

Regardless of why you are using BACKUP TO URL, one of the problems that you are likely to run into at some point is the dreaded:

                Msg 3271, Level 16, State 1, Line 60
                A nonrecoverable I/O error occurred on file
                “https://somestorageaccount.blob.core.windows.net/backups/somedatabase.bak: “ Backup
                to URL received an exception from the remote endpoint. Exception Message: The remote
                server returned an error: (412) There is currently a lease on the blob and no lease ID was
                specified in the request…
                Msg 3013, Level 16, State 1, Line 60
                BACKUP DATABASE is terminating abnormally.

Applications using Azure storage can take leases on files that are held in the storage containers. This avoids issues with other applications concurrently changing (or even deleting) files that the application needs. The BACKUP TO URL feature in SQL Server takes an infinite lease on the backup file that it creates. That lease is removed when the backup completes. (The process of dealing with leases in Azure Storage is described here).

However, if you interrupt a backup (or network issues interrupt it for you), and this is a prolonged interruption, the lease can remain and when you try to overwrite that backup blob (or even delete it), you’ll see the error above.

Now, the design feature that makes this a bit easier to deal with is that the BACKUP TO URL command always uses a well-known lease ID: BAC2BAC2BAC2BAC2BAC2BAC2BAC2BAC2

What is needed to delete it then, is a tool that can break leases, or to run a PowerShell  script as described in this article. You should also take this as yet another hint to learn about PowerShell if you haven’t done so already.

Details about other aspects of BACKUP TO URL troubleshooting are given in this article.

2014-07-09