Step-By-Step: Backing Up SQL Databases to Azure

  • Article

BrokenTape

While delivering an CANITPRO Camp not to long ago, an IT administrator shared his story as to why he had decided to move his organization's SQL backup to the cloud.  It seemed that his predecessor was very diligent in switching the backup tapes every morning.  He insured backups were created daily, weekly and monthly as well and locked the tapes in a fire proof safe at his home nightly.  One day the organization he worked for has a massive fire and the server room suffered massive damage.  When the servers were replaced, the IT administrator confidently inserted the backup tape only to find that the tape was empty.  The same was true of the weekly and monthly tapes as well.  All the tapes, 2 years worth of backups, stored in the home safe were blank.  It was later realized that the safe used a magnetic lock which wiped or corrupted all the tapes contents every time the safe was locked.

Scenarios such as these occur more often than most realize.  A great way to eliminate the need for tapes is by utilizing the cloud in a backup scenario.  This Step-By-Step, provided by Keith Myer, will take us through instructions on SQL Server 2012 SP1 CU2 native backup capabilities to allow for database backups to be created on Windows Azure.  This solution will allow you to eliminate the requirement for tape backup utilizing a secure online backup solution which can provide instant visibility to your database backups.

SQL Server 2012 Service Pack 1 Cumulative Update 2 provides the ability to backup SQL databases and logs to Windows Azure cloud storage via native SQL Server Backup.  This backup is conducted by both SQL Server Management Objects ( SMO) and Transact-SQL ( T-SQL). Cloud storage backup is a great disaster recovery insurance policy since backups, when completed, are instantly located offsite. What more is that the pay scale of cloud storage economics provides a cost effective solution since Windows Azure storage costs are less than $100/TB per month.  Geo-redundant storage based on current published costs as of this article’s date which costs less than a couple SDLT tapes. Microsoft also provides a current pricing model for Windows Azure Storage via a Price Calculator to ensure the economics meet your organizations needs.

Prerequisites

  1. Sign-up for a 90-day trial of Windows Azure so that the steps included can be completed.
     
    NOTE: When signing up for the process, credit card information will be requested to confirm that you are a legitimate free trial subscriber. Credit card information is only used to confirm identity and will NOT be charged for any Windows Azure services unless the trial subscription is explicitly convert into a paid subscription at a later date. 
     
    Should you currently have a paid subscription or MSDN subscription for Windows Azure, please ensure that you have activated the Windows Azure Virtual Machines and Virtual Networks Preview Feature. When signing up for a new free trial account, this feature will automatically be activated.
  2. Download Cumulative Update 2 for SQL Server 2012 Service Pack 1 and apply it to the SQL Server instance. 

 

Step 1: Provisioning Cloud Storage

  1. Launch the Windows Azure Management Portal and login with the credentials used when activating your Windows Azure 90-Day trial above. 
     
  2. Click Storage in the left navigation pane of the Windows Azure Management Portal.
     
    image
    Windows Azure Management Portal – Storage Accounts
     
  3. On the Storage page of the Windows Azure Management Portal, click +NEW on the bottom toolbar to create a new storage account location.
     
    image
    Creating a new Windows Azure Storage Account location
     
  4. Click Quick Create on the New > Storage popup menu and complete the fields as listed below:
     
    - URL: XXXbackup01 ( where XXX represents your initials in lowercase )
     
    - Region / Affinity Group: Select an available Windows Azure datacenter region for your new Storage Account. 
     
    NOTE: Because you will be using this Storage Account location for backup / disaster recovery scenarios, be sure to select a Datacenter Region that is not near to you for additional protection against disasters that may affect your entire local area.
     
    Click the Create Storage Account button to create your new Storage Account location.
     
  5. Wait for your new Storage Account to be provisioned. 
     
    image
    Provisioning new Windows Azure Storage Account
     
    Once the status of your new Storage Account shows as Online, you may continue with the next step.
     
  6. Select your newly created Storage Account and click the Manage Keys button on the bottom toolbar to display the Manage Access Keys dialog box.
     
    image
    Manage Access Keys dialog box
     
    Click the image button located next to the Secondary Access Key field to copy this access key to your clipboard for later use.
     
  7. Create a container within your Windows Azure Storage Account to store backups.  Click on the name of your Storage Account on the Storage page in the Windows Azure Management Portal to drill into the details of this account, then select the Containers tab located at the top of the page.
     
    image
    Containers tab within a Windows Azure Storage Account
     
    On the bottom toolbar, click the Add Container button to create a new container named “backups”.

 

Step 2: Performing the SQL Cloud Backup

  1.  Launch SQL Server Management Studio and connect to your SQL Server 2012 SP1 CU2 database engine instance.

  2. In SQL Server Management Studio, right-click on the database you wish to backup in the Object Explorer list pane and select New Query.
     
    image
    SQL Server Management Studio
     

  3. In the new SQL Query Window, execute the following Transact-SQL code to create a credential that can be used to authenticate to your Windows Azure Storage Account with secure read/write access:

    CREATE CREDENTIAL myAzureCredential
    WITH IDENTITY='XXXbackup01',
    SECRET= ’PASTE IN YOUR COPIED ACCESS KEY HERE' ;

    Prior to running this code, be sure to replace XXXbackup01 with the name of your Windows Azure Storage Account created above and paste in the Access Key you previously copied to your clipboard.

  4. In the SQL Query Window, execute the following Transact-SQL code to perform the database backup to your Windows Azure Storage Account:
     
    BACKUP DATABASE database_name TO
    URL='https://XXXbackup01.blob.core.windows.net/backups/database_name.bak'
    WITH CREDENTIAL='myAzureCredential' , STATS = 5;
     
    Prior to running this code, be sure to replace XXXbackup01 with the name of your Windows Azure Storage Account and replace database_name with the name of your database.
     
    Upon successful execution of the backup, you should see SQL Query result messages similar to the following:
     
    image
    Successful Backup Results

 

Once completed, backups are transported offsite to Windows Azure Storage via an authenticated SSL-encrypted network communications path. The actual backup data stored on the Windows Azure Storage platform can also be encrypted if Transparent Data Encryption (TDE) is enabled on your original SQL databases. Details on enabling Transparent Data Encryption in the TechNet Library.

 

Restoring the SQL Database is a easy as it was to back up.  Simply use the following Transact-SQL syntax to restore the SQL Database back from Windows Azure:

 

RESTORE DATABASE database_name FROM
URL='https://XXXbackup01.blob.core.windows.net/backups/database_name.bak'
WITH CREDENTIAL=’myAzureCredential’, STATS = 5, REPLACE