SQL Server 2014: Backup to Azure Blob April 14th, 2014

Vinod Kumar

SQL Server 2014 has this new capability which allows us to take a backup to a URL. Infact, it is available in SQL 2012 SP1 CU2 itself – I lied to you in my first statement :). Anyways, I thought it is worth a mention and a blog for itself.

The concepts of accessing the keys have been mentioned also at: Viewing Windows Azure Storage using SSMS.

Copy the keys and we are ready.

Now that you have these key’s handy. We will take an encrypted backup as explained in SQL Server 2014: Encrypted Backups and upload it into Azure Blob storage rather than our conventional disk subsystem. So here are the steps to follow.

Create your credential

The create credential command has been there since SQL Server 2005 but I never found a better scenario as this to use the same. So here I am sort of creating my proxy name to access the blob storage account.

CREATE CREDENTIAL mycredential

WITH IDENTITY= ‘mtcvinod’

, SECRET = ‘<< INSERT YOUR KEY FROM PORTAL >>’

GO

Backup your database to Azure

The next step is to backup your database. This command is ALMOST similar to a normal backup statement with the ONLY change where we point to a URL rather than a disk location. Otherwise everything is same.

BACKUP DATABASE[MySecureDB]

TO URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’

WITH CREDENTIAL = ‘mycredential’,

  NOFORMAT, NOINIT,

  NAME = N’mySecureDB – compressed, encrypted’,

  SKIP, NOREWIND, NOUNLOAD, COMPRESSION,

  ENCRYPTION (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = BackupCert

   ),

  STATS = 10;

GO

If you are playing around with this script again and again, make sure you delete the file that got uploaded the previous time. Else we will be presented with the below error 3271. Alternatively you can add the WITH FORMAT option too.

Msg 3271, Level 16, State 1, Line 7
A nonrecoverable I/O error occurred on file http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak: The file http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed..
Msg 3013, Level 16, State 1, Line 7
BACKUP DATABASE is terminating abnormally.

Restore from URL

Just like we did a backup, now we can also do a restore using a similar command with the change being the URL instead of a Disk location.

RESTORE DATABASE [MySecureDB]

FROM URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’

WITH CREDENTIAL = ‘mycredential’

, STATS = 5

GO

To Viewing Windows Azure Storage using SSMS, read that blogpost or feel free to use other Azure Blob tools like the one I have used here is Cloudberry or any others of your choice.

There are a number of other error messages which are self-explanatory which need a mention here. If you are going to restore from this backup on a database which is running currently, you might get the below error:

Msg 3159, Level 16, State 1, Line 21
The tail of the log for the database "MySecureDB" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

If the backup URL mentioned doesnt exist in the given location then we will get the blow error:

Msg 3271, Level 16, State 1, Line 21
A nonrecoverable I/O error occurred on file "http://vinod.blob.core.windows.net/backup/04-mySecureDB_compress_encrypt.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage.

PS: Note, the above example uses encrypted backups – but the same can be achieved using normal backups too.

In my subsequent blogs, let me also bring out some of the other errors one might get by using Encrypted Backups.

Tags: , , , , , , , , ,

This entry was posted on Monday, April 14th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


4 Responses to “SQL Server 2014: Backup to Azure Blob”

  1. Carles says:

    Vinod, good afternoon

    Thanks a lot for your post and information about blob.
    would this service be able to be done in a VM ? so, I would like to upload my BAKcup file from local (sql server 2014) to cloud (sql server 2014) and recover from local.

    With your method I’m able to upload into a blob, but from my VM I cannnot see the file uploaded :(

    thanks a lot for your blog and time!

    Carles

    • Vinod Kumar says:

      If you intentions are to upload into a VM, then it is not the service to look out for. If you want to read the blob storage, use some of the free Azure Storage Explorers available. You can also restore from a Blob to your on-prem or Azure IaaS SQL Server.

  2. J says:

    Hi

    Thanks for the article.
    With reference to the same and with reference to the url(http://blogs.technet.com/b/microsoft_blog/archive/2014/04/10/privacy-authorities-across-europe-approve-microsoft-s-cloud-commitments.aspx) referring to privacy authorities across Europe approving Microsoft’s cloud commitments, do we have any standard tools that would do the backup of SQL Server database from a data center.

    The organization currently uses Veritas NetBackup SQL Agent to have the backup done to the DataDomain.

    Please advise.

    Regards,
    J

    • Vinod Kumar says:

      These are OOB features of SQL Server. Now third party utilities need to support by extending their offering. So I am personally not sure if these are available with NetBackup Agent.

Leave a Reply