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.

    image

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

    image

  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.

    image

  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.

    image

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

    image

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

    image

  8. Storage Account Primary Access Key and Container URL will be needed in 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

      image

    • 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 container as shown below

      image

 

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 = 'http://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.

    image

 

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 = 'http://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