THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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.

Published Wednesday, July 09, 2014 12:16 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Arthur Olcot said:

Hi Greg...

I used to get this a lot but MS must have done some work in CU4 or CU5 (I can't remember which) which for me at least seemed to eliminate the problem totally.. The problem was so frequent I originally developed essentially the same "break lease" logic in C# so that I could run it each morning if necessary: http://bit.ly/1pZYPjK

July 9, 2014 4:05 PM
 

Greg Low said:

Yes definitely still happening and also important if you want to remove a partial blob rather than overwrite it.

July 9, 2014 5:22 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement