Share via

Backup All Your SQL Server Databases to the Cloud with SQL 2012

  • Article

SQL 2012 SP1 CU2  has introduced support for backup to Azure Cloud storage right in the database engine.  This is cool because Azure storage is low-cost, offsite and optionally geo-redundant.  So this can be a simple way to protect your data against a variety of failures.  If your database is running on-premises backup to the cloud should not replace local backup.  Local backup is much faster, and can be performed much more often than cloud backup.  But you might consider a secondary job to take full backups to the cloud on a less frequent basis.

 

The BOL for backup to Azure is here Server Backup and Restore with Windows Azure Blob Storage Service.  This covers the basic syntax, the Azure setup and billing considerations.

Here’s the SQL team blog on this: Backup and Restore to Cloud Simplified in SQL Server 2012 SP1 CU2

I just wanted to add a  simple script to take a full, compressed, copy_only database backup of multiple databases to your Azure storage account (and note also the new text functions FORMAT and CONCAT that make this script much cleaner than it would have been before):

 
/* create a credential to store your secret Azure key

create credential AzureBackupCredential
 with identity = 'por...wtq7rlyhd', --your storage account
      secret = 'jskl...daflvn3489723u4p=='  --your storage account key

*/
declare @credentialName sysname = 'AzureBackupCredential';
declare @containerName nvarchar(1000) = 'backup';
declare @storageAccount nvarchar(4000) = 
  (select credential_identity 
   from sys.credentials 
   where name = @credentialName)


declare @name sysname;
declare dbc cursor local for
  select d.name
  from sys.databases d
  where d.name not in( 'tempdb' )

open dbc;

fetch next from dbc into @name
while @@FETCH_STATUS = 0
begin

  declare @url nvarchar(max) = concat(
        'https://', @storageAccount, '.blob.core.windows.net/'
        , @containerName,'/', replace(@@SERVERNAME,'\','-'),  '/'
        , @name, '/', year(getdate()), '/'
        , format(month(getdate()),'00'),'/'
        , @name, '.', format(getdate(),'yyyy-MM-dd.HHmmss')
        , '.bak');

  declare @sql nvarchar(max) = concat('
  backup database [', @name, '] 
  to url=''', @url, '''
  with compression, copy_only, stats=10, credential='''
        , @credentialName, ''';');
  
  print @sql
  exec (@sql);

  fetch next from dbc into @name;
end;
close dbc;
deallocate dbc;


  

  

One thing to note is if you abort a backup, you will see a BLOB for the failed backup in Azure Storage that you won’t be able to delete it through the Azure portal.  It looks like it’s a 1TB blob in the Azure portal, but you’re only charged for the blocks actually written to the blob.  To delete the failed backup see  Deleting Backup Blob Files with Active Leases.

 

David

Comments

  • Anonymous
    January 28, 2013
    Hi David, great article. One clarification about the last paragraph. You can delete a blob with an active lease. An authorized request can break the lease without supplying a lease ID. See msdn.microsoft.com/.../ee691972.aspx for more information.
  • Guy Bowerman

  • Anonymous
    January 28, 2013
    @Guy cool, thanks.  Off to write a some SQL CLR Wrappers for the Azure BLOB storage REST API.

  • Anonymous
    January 28, 2013
    Here's the corresponding MSDN article for SQL backup blob files: msdn.microsoft.com/.../jj919145.aspx