SQL Tip-10 | SQL Server Database Backup to Cloud (Microsoft Azure)

This feature can help you leverage the power of Cloud with your SQL instance and taking your backups directly on Cloud.

Backup to URL is not new to SQL 2014, it was first introduced in SQL Server 2012 SP1 CU2, but you were allowed to use the URL in query only. Starting with SQL Server 2014 this feature has been totally integrated, now you can specify URL while taking Backup or doing Restore no matter if you are performing the task via Query, Backup\Restore GUI, Powershell cmdlets or using SMO.

To be able to take backups or restore from Microsoft Azure (earlier named as Windows Azure) you must create a Credential. Before creating a credential you must already have a Storage account created on Azure. Once you create your storage account you’ll be able to get Storage Access Key from Azure portal. This Storage access key will provide full Admin control on your storage account.

Here is the script to create the Credential:

Create credential Storage_4Backup

with identity = ‘dbbackup12’,

SECRET = ‘H61bUxq5ld+AUFGa3Zc30CKYGSOyMDGEb/MKcCjvssdrgF34PZt+TV/42/HoJpOgnrM82KQpuCYYHEudjg==’

go

 

You need to replace the Secret key with your storage access key, the screenshot below will tell you from where you need to copy this.

Once the credential is created on your SQL instance you are now all set to be able to take backups or do restore from Windows Azure storage account.

Here is the T-SQL Script for the same:

Backup database azure_test

to URL = ‘http://dbbackup12.blob.core.windows.net/sqlprod/azure_test.bak’

with credential = ‘Storage_4Backup’, format, compression, stats=10, MediaName = ‘azure_test_22_02_2014’,

mediadescription=‘Backup_of_azure_test’

 

Note: To get the complete script bundled with step-by-step demo follow this link.

You can also do the backup to windows azure using Backup UI – but the credential is a must, here is the screenshot.

If the credential already exists you can select from the drop down menu otherwise the wizard itself gives you an option to create one.

Rest everything is self-explanatory. 😉

 

Thanks,
Sarabpreet Singh