SQL SERVER – Steps to Backup to Windows Azure storage

Moving to a cloud-based world is inevitable, it is something we need to learn soon. Ever since SQL Server 2014 has been released, the concept of uploading your backups to Blobs with Windows Azure has been around. In reality, performing a backup or restore operations with SQL Server 2012 SP1 CU4 and later requires no additional tools actually, and be done with either T-SQL or SSMS. This blog describes how to perform backup operations with T-SQL. This will be part of a series of blogs to come in the future. Let me walk through the initial steps.

Creating Credentials

To perform the backup and restore procedures on your local SQL Server you will need to create a SQL credential using the Windows Azure Storage Account configuration. The following steps will create the necessary credential:

1. Connect to SQL Server Management Studio.
2. On the Standard toolbar, click New Query.
3. Copy and paste the following example into the query window, modifying as needed.

CREATE CREDENTIAL mycredential
WITH IDENTITY= 'mystorageaccount' --this is the name of the storage account you specified when creating a storage account
, SECRET = '<storage account access key>' -- this should be either the Primary or Secondary Access Key for the storage account.

Steps to backup to Windows Azure storage

The following steps describe how to perform a backup of a database to the Windows Azure storage service. The database can be an on-premises database or located in a Windows Azure Virtual Machine. The key requirement for this demo variation is that the database must be accessible from SQL Server Management Studio:

If you don’t have your own database, you plan to use for this tutorial, then install AdventureWorks from Download and Install AdventureWorks 2014 Sample Databases

1. Connect to SQL Server Management Studio.
2. In the Object Explorer, connect to the instance of SQL Server on which the database to be backed up is located.
3. In Object Explorer, connect to the instance of the Database Engine that has the database you plan to back up.
4. On the Standard menu bar, select New Query.
5. Copy and paste the following example into the query window, modify as needed, and click Execute.

BACKUP DATABASE [AdventureWorks2014]
TO URL = 'https://mystorageaccount.blob.core.windows.net/privatecontainers/AdventureWorks2014.bak'
/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/
WITH CREDENTIAL = 'mycredential';
/* name of the credential you created in the previous step */
GO

As you can see, the steps to back up to an URL is simple as described above. The pre-requisite for this is to have a storage account in Azure ready before you do the same.

Do let me know if you have ever taken a backup to Azure to date. Please let me know if you want me to write on this topic in the future.

Reference: Pinal Dave (https://blog.sqlauthority.com)