SQL Server backup to URL (Windows Azure) w/ T-SQL

In this article, we will present you the main idea behind the SQL Server backup to URL feature which has been available since the 2012 SP1 CU2 version. 

This backup operation can be performed either from Powershell, T-SQL, or SMOs (SQL Server Management Objects), but in this article we will present you just the version with T-SQL.

Just before we get started, I would like to tell you that all of the steps that you would have to make, either through Powershell or T-SQL or SMOs (C#) in order to make your backup to a URL (Windows Azure) or other storage mediums are simplified in an easy and quick process of backing up your databases through a ubiquitous web-application, sqlbak.com.

We recommend that you check out the application and at least try our free trial service and see for yourself how much easier and convenient it is afterward to do backups.

Backup to URL

So, if you have chosen to do everything yourself, then below you will find the necessary steps which you have to make in order to backup to Windows Azure storage service.

Before we jump right into the SQL code, there are some things that should be mentioned and you should be aware of when configuring your Azure storage service and your database.

• • • First of all, when you create a container for Azure Blob storage you should set the access to that container to private. This will restrict access to all users and accounts which could be able to provide the required information to successfully authenticate in your Azure account.
    • The user that will make BACKUP and RESTORE requests to Azure has to be part of the db_backup operator database role and have access to ALTER ANY CREDENTIAL.
    • Azure will require you to store the account name of your Azure Blob storage and the authentication key on your SQL Server machine, in a SQL Server Credential. With this, it will connect to your Azure account and make the backup or restore operations.

Also, a short overview of what are the components of Azure, if you have never used this service before, is in order. We will explain in a few words its main elements, which we also have been or will refer to later in this article.

• • Blob: is actually any type of file, of any size. Only two types of blobs can be stored in Azure: block and page blobs.
    • You can address a blobby an URL with this format:
    • https://[account name].blob.core.windows.net/[container]/[blob] (although https is not mandatory, it is recommended)
  • Container: is similar to a folder as it allows for the grouping of multiple blob files and can store an unlimited number of blob files. In order to have a backup created on Azure storage, you will need to have created at least the root container.

Now, moving on to the internals of SQL Server, we will also explain a few elements that we have mentioned and will continue to mention in this article.

• • Credential: is the object that is used for the authentication process which is required when you want to connect to an external resource, outside SQL Server.
    • It stores the account name and access key of your Azure storage. Once this credential has been created it must be used by specifying the WITH CREDENTIAL clause whenever you’re running BACKUP or RESTORE queries.
    • In case you want to find out more information about how you can copy or regenerate access keys for your Azure account, look at this article.
    • If you want to find out how you can create or manage your credentials, review this article.

The URL that uniquely identifies your backup file and which you specify in your T-SQL query in SQL Server has the following format:

https://[account name].Blob.core.windows.net/[CONTAINER]/[backupFile.bak]

Now that we have the overview of what is required to do on our side, we should also be aware of the limitations of Azure’s storage:

• • Backup files cannot exceed 1Tb in size.
  • Backup and restore statements can only be sent through T-SQL, Powershell, or SMO’s. Currently, there is no support to do this from SSMS.
  • You cannot append a backup to an existing blob. You can only overwrite a blob by using the WITH FORMAT clause.
  • Backing up to multiple blobs in just one query is not supported. Something like the query below will issue an error:

    BACKUP DATABASE myDB 
    TO URL = 'https://myacct.blob.core.windows.net/Container1/myDB_1.bak'
       URL = 'https://myacct.blob.core.windows.net/Container1/myDB_2.bak'
    WITH CREDENTIAL = 'AzureCredentials',
    STATS = 5

• • Block size cannot be specified with the BACKUP statement.
  • MAXTRANSFERSIZE is also not supported.
  • Expiration dates for your backups by using RETAINDAYS and EXPIREDATE are not supported.
  • Also, there are limitations in terms of the length of names you can issue for the account name, blob, and container as the entire URL is limited to a number of 259 characters, out of which 36 characters are used by the standard parts of the URL, which leaves you with 223 characters for your account name, container and blob file name combined.

Also, in terms of what operations are supported (BACKUP and RESTORE) and with what arguments they can be run, you can find the full list here.

Now, after a long but necessary introduction to the internals of SQL Server and Azure’s blob service, we can start with the T-SQL queries that are going to be used.

• • First, we will have to create a credential on our SQL Server, which we will do with a query similar to the one below:

    IF NOT EXISTS 
    (SELECT 1 FROM sys.credentials 
    WHERE credential_identity = 'myAzureCredentials')
    CREATE CREDENTIAL myAzureCredentials WITH IDENTITY = 'myAzureAccount'
    ,SECRET = '<myStorageAccessKey>'

Full database backup to URL

• • Now that we have our credential set up we can proceed to the next step, which is to back up a complete database, using a query similar to the one below.

    BACKUP DATABASE myDB 
    TO URL = 'https://myAccount.blob.core.windows.net/Conntainer/myDB.bak'
          WITH CREDENTIAL = 'myAzureCredentials' 
         ,COMPRESSION
         ,STATS = 1

For those of you not familiar with the STATS option, it displays a message after each percentage is is completed, a percentage which is specified as the stats value, in the case of the query above, it’s 1 (one).

So, after each 1% has been completed, we will be prompted with a message informing us about this. You can obviously change it to something higher like 5 or 10 in order to be prompted fewer times.

Database differential backup to URL

• •  You might also want to back up the database’s log file, and you can do this by using a query similar to the next one.
  • But, remember that your database must be under the FULL recovery model in order to make transaction log backups, which is why the first part of the query changes the recovery mode of the database.
  • If you want to manually check or change the recovery model from SSMS, check out this article, which shows you how to do just that.

-- In order to backup the transaction log, our database must be
-- under the FULL RECOVERY model. If you did not change it by
-- following the steps in our article, you can do this with the
-- next T-SQL statement

USE master
ALTER DATABASE myDB SET RECOVERY FULL

-- After changing the recovery model of the database, start by
-- doing a FULL backup of your database. Create a file name for
-- the backup and append a timestamp to its name, like this.

DECLARE @FullName AS NVARCHAR (259);
SET @FullName = 'https://myAccount.blob.core.windows.net/Container1/myDB_FULL_'+ 
REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.bak';

-- The result will be something similar to this
https://myAccount[...]net/Container1/myDB_FULL_2014_03_30_18_57_26.bak

-- Now after we've set the name of the file, we will proceed to
-- backing up the database

BACKUP DATABASE myDB
    TO URL = @FullName
WITH CREDENTIAL = 'myAzureCredentials';
    ,COMPRESSION

-- The next step after backing up the database is to back up the log
-- We are going to proceed in a similar way as we did for the main
-- backup. We will create a filename for the log and back up it
-- in a similar way

DECLARE @LogName AS VARCHAR (259)
SET @LogName = 'https://myAccount.blob.core.windows.net/Container1/myDB_Log_'+
REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120),  '-','_'),':', '_'),' ', '_') + '.trn';

-- After setting up the file name we will just run a log backup
-- command to the URL filename we just defined

BACKUP LOG myDB
    TO URL = @LogName
WITH CREDENTIAL = 'myAzureCredentials'
    ,COMPRESSION;

Backup the primary filegroup to URL

You might have already gotten the hang of it up until this point. All you have to do is to add the TO URL and

-- Back up the files of the primary filegroup

BACKUP DATABASE myDB
   FILEGROUP = 'Primary'
   TO URL = 'https://myAccount.blob.core.windows.net/Container1/myDBfiles.bck'
   WITH CREDENTIAL = 'myAzureCredentials'
       ,COMPRESSION;

Create a differential backup of the primary filegroup to URL

Doing a differential backup of the primary filegroup is similar to doing a standard differential backup, as you can see below:

-- Doing a differential backup of the primary filegroup is just
-- like the standard differential backup command

BACKUP DATABASE myDB
   FILEGROUP = 'Primary'
   TO URL = 'https://myAccount.blob.core.windows.net/Container1/myDBfilesdiff.bck'
   WITH 
      CREDENTIAL = 'myAzureCredentials'
      ,COMPRESSION
  ,DIFFERENTIAL;

Restore a backup from the URL and move the files

Now that we have all of our backups in place, we will at some point want to do a reverse operation and restore our data. The next queries will show you how to do a restore if your data has been stored with Windows Azure.

Assuming that the installation paths on the machine SQL Server are running on are standard, you can pretty much use the queries below, just change the URL and other specific details.

-- Of course we will have to back up the tail of the transaction
-- log first. We will proceed just like we did with other backups,
-- in the sense that we will define a filename and then do the
-- backup operation

DECLARE @LogName AS VARCHAR (259);
SET @LogName = 'https://myAccount.blob.core.windows.net/Container1/myDB_Log_'+
REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.trn';

BACKUP LOG myDB
    TO URL = @LogName
WITH CREDENTIAL = 'myAzureCredentials'
    ,NORECOVERY;

-- Right after we have done this we will proceed to the actual
-- restore query

RESTORE DATABASE myDB FROM URL = 'https://myAccount.blob.core.windows.net/Container1/myDB.bak'
WITH CREDENTIAL = 'myAzureCredentials'
    ,MOVE 'myDB_data' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\myDB.mdf'
    ,MOVE 'myDB_log' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\myDB.ldf'
    ,STATS = 5

Restore to a point in time from URL

Now, the last part of this article would be to show you how to do a point in time restore of your data, from your online backups.

If you want more information about what point in time restores are and how they are made with either T-SQL or with SSMS, please check our articles for T-SQL here and for SSMS here.

Now, getting down to the final step in this article, the query below will be a guide for you on what to do to restore to a point in time from URL:

-- Assuming we have a transaction log also to restore we will
-- use this situation for our example. Otherwise, if your database
-- is not under such a recovery model, you would only need to run
-- the first query

RESTORE DATABASE myDB FROM URL = 'https://myAccount.blob.core.windows.net/Container1/myDB.bak' 
WITH 
    CREDENTIAL = 'myAzureCredentials'
    ,MOVE 'myDB_data' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\myDB.mdf'
    ,MOVE 'myDB_log' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\myDB.ldf'
    ,NORECOVERY
    ,STATS = 5

-- While restoring the log file is the place where we specify
-- what time the restore operation to stop at. The last succesful
-- transaction included in the time you want to restore to or before
-- that is were the data will be restored to.

RESTORE LOG myDB FROM URL = 'https://myAccount.blob.core.windows.net/Container1/myDB.trn' 
    WITH CREDENTIAL = 'myAzureCredentials'
    ,RECOVERY 
    ,STOPAT = 'Dec 22, 2011 3:52 PM'

Also, be aware that when doing a point in time restore you can restore an older full backup, but you will have to restore all of the sequential logs up until the target time.

As a best practice, for all log restores you should specify the same target time in the STOPAT clause in order to make sure that no data is restored that is from a time after your target time.