Tuesday, May 10, 2016

SQL Server Backup to URL

Although this was introduced with SQL Server 2012, as per my experience, the usage of it is very low because it is unknown to many or do not use this facility to have a good backup strategy. When maintaining an important database, an implementation of a backup strategy is a must and it should be based on RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

Backup strategy is a part of Disaster Recovery solution. If you worry about the space for holding the backup, protecting backups from disasters, and securing your backups, one of the best options that can be used is Backup to URL.

Setting it up is very simple. You need to make sure that you or your company has an account with Azure and you have a storage created. Make sure a container is created in your storage for holding backups.

First, you need to create a Credential in your SQL Server instance for connecting with your Azure Storage. You can either use TSQL or GUI given under Security section.

Once the credential is created, it can be used for backing up your database to Azure Storage. Again you can use either TSQL or GUI.

TO URL = 'https://dinesqlstorage.blob.core.windows.net/sqlbackups/Sales_20160512.bak' 
      WITH CREDENTIAL = 'AzureCredential' 
     ,STATS = 5;​

Once backed up, if you check your storage, you should see the file;

Note that most of the options that are available with general backup statement are available with backup to URL too. However some of the significant differences are;
  • Appending to an existing backup file is not possible (INT | NOINT is ignored)
  • MIRROR TO option is not supported
  • Generally backup device name is allowed with 259 characters. This option needs 36 characters for azure URL and leaving 223 characters to specify name of the account, container and backup name.

No comments: