Step by Step : Backup and Restore for SQL Server in Windows Azure Blob Storages

As all of you already awared, backing up data is a vitally important part of the strategy in protecting data loss due to disasters such as loosing data center completely. This article is not a pure disaster recovery article and not talks about disaster recovery strategies. In this article we are going to talk about a new cool feature comes with SQL Server 2012 SP1 + Cumulative Update 2 which is called “Backup to URL” and what the benefits of this feature are and how we can use it.

With SQL Server 2012 SP1 + Cumulative Update 2 you can back up to and restore from the Windows Azure Blob storage service directly by using T-SQL backup and restore statement. By using this feature you can easily backup your application databases to Windows Azure Blob Storage and provide a disaster recovery solution to your applications. You can use this article for additional information.

Below is the outline of the steps:

1. Create the Windows Azure Storage Account in Windows Azure
2. Create the Credential
3. Backup the on-premise database to Windows Azure Blob Storage
4. Restore the database from Windows Azure Blob Storage

Create the Windows Azure Storage Account in Windows Azure

1. First of all, you need a windows azure storage account to store the backup files. In order to create Storage Account connect to Windows Azure Portal

2. Once connected to portal, click Storage in the left menu and then click the New button at the lower-left corner of the page as shown below.

   

3. Click Data Services, then click Storage and then click Quick Create as shown below

   

4. Specify URL and Location/Affinity Group and then click Create Storage Account as shown below. It may take some time for the storage account to be created.

   

5. Once Storage Account has been created, you need to create a Container in this storage account. In order to do that go to Windows Azure Portal click Storage, then click sqlserverbackups storage account, and then click Containers as shown below.

   

6. Click Create a Container and specify Name and Access and then click Tick Button as show below

   

7. Once the create container operation is done, it is shown in the containers list as shown below.

   

8. Storage Account Primary Access Key and Container URL will be neededin the following Create the Credential and Backup the on-Premise Database to Windows Azure steps. So we need to retrieve this information from Windows Azure portal.

   • In order to get Storage Account Primary Access Key, click Storage, then click sqlserverbackups storage account, and then click Manage Access Keys at the bottom of the page and then copy the Primary Access Key as shown below

     

   • In order to get Container URL, click Storage, then click sqlserverbackups storage account, and then click Containers at the top of the page and then copy the URL of backuptourldemo containeras shown below

     

Create the Credential

1. Before backing up the database, you need to create a credential for the Windows Azure Storage Account.
2. In order to do that, change the below script with appropriate values and then execute. You need to replace;

   • Identity value with Windows Azure Storage Name (It’s sqlserverbackups for this demo)

   • Secret value with your Storage Account Primary Access Key         

     --Create a credential for windows azure storage account
     --Identity : Windows Azure Storage Account Name
     --Secret : Storage Account Primary Access Key

     CREATE CREDENTIAL mycredential
                     WITH IDENTITY = 'sqlserverbackups'
                     ,SECRET = '4DLmAKvFWpP9ptMgkySsoHOWB5uE2uBRzJxHP22z5GTDmk7AXodZO13gzGR1';

Back up the on-premise Database to Windows Azure Blob Storage

1. Connect to SSMS and create a sample database

   --Create a Sample Database
   CREATE DATABASE DB1
   GO
   USE DB1
   GO
   CREATE TABLE tbl1 (col1 int, col2 varchar(20))
   GO

2. After that you can back up your database by using below script. Before executing it you need to replace;

   • URL value with your Container URL

     --Backup database to Windows Azure Blob Storage
     BACKUP DATABASE DB1
                     TO URL = 'https://sqlserverbackups.blob.core.windows.net/backuptourldemo/DB1.bak'
                     WITH CREDENTIAL = 'mycredential'
                     ,STATS = 5;

3. Execute the backup script. It may take some time for the database to be backed up.

4. After backup operation is completed check the container if the backup file is there. In Windows Azure portal click Storage, then click sqlserverbackups storage account, then click Containers, and then click backuptourldemo container and observe that DB1.bak backup file is there as shown below.

   

Restore the Database from Windows Azure Blob Storage

1. You can restore a database into on-premise Database Engine by using backup file in Windows Azure Blob Storage

2. Before executing the restore statement, you need to ensure that credential is created. If not use below script to create the credential

   --Create a credential for windows azure storage account
   --Identity : Windows Azure Storage Account Name
   --Secret : Storage Account Primary Access Key

   CREATE CREDENTIAL mycredential
                   WITH IDENTITY = 'sqlserverbackups'
                   ,SECRET = '4DLmAKvFWpP9ptMgkySsoHOWB5uE2uBRzJxHP22z5GTDmk7AXodZO13gzGR1+akJgXWynq9PkIdPNZxCsxeNxw==';

3. Now you can restore the database by using below script.

   --Drop database
   use master
   go
   drop database db1

   --Restore DB1 from backup in Windows Azure Blob Storage
   RESTORE DATABASE DB1
                   FROM URL = 'https://sqlserverbackups.blob.core.windows.net/backuptourldemo/DB1.bak'
                   WITH CREDENTIAL = 'mycredential'

That’s it. We covered all steps you need to follow for using Windows Azure Blob Storage as database backup target.

As review, with SQL Server 2012 SP1 + Cumulative Update 2 you can back up to and restore from the Windows Azure Blob storage service directly by using T-SQL backup and restore statement. The steps you need to follow are;

1. Create the Windows Azure Storage Account in Windows Azure
2. Create the Credential
3. Backup the on-premise database to Windows Azure Blob Storage
4. Restore the database from Windows Azure Blob Storage