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
We have been using this in a variety of ways from
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
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:
Level 16, State 1, Line 60
A nonrecoverable I/O error
occurred on file
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
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