SQL Server 2012 SP1 Cumulative Update 2 includes new functionality that simplifies the backup and restore capability of an on-premises SQL Server database to Windows Azure. You can now directly create a backup to Windows Azure Storage using SQL Server Native Backup functionality. Read the information below to get a brief introduction to the new functionality and follow the links for more in-depth information.
To download the update, go to the SQL Release Services Blog or to Download SQL Server 2012 SP1 CU2.
In addition to disk and tape you can now use SQL Server native backup functionality to back up your SQL Server Database to the Windows Azure Blob storage service. In this release, backup to Windows Azure Blob storage is supported using T-SQL andSMO. SQL Server Databases on an on premises instance of SQL Server or in a hosted environment such as an instance of SQL Server running in Windows Azure VMs can take advantage of thisfunctionality.
The Windows Azure pricing calculator can help estimate your costs.
Storage: Charges are based on the space used and are calculated on a graduated scale and the level of redundancy. For more details, and up-to-date information, see the Data Management section of the Pricing Details article.
Data Transfers: Inbound data transfers to Windows Azure are free. Outbound transfers are charged for the bandwidth use and calculated based on a graduated region-specific scale. For more details, see the Data Transfers section of the Pricing Details article.
Backup to Windows Azure Storage is engineered to behave much like a backup device (Disk/Tape). Using the Microsoft Virtual Backup Device Interface (VDI), Windows Azure Blob storage is coded like a “virtual backup device”, and the URL format used to access the Blob storage is treated as a device. The main reason for supporting Azure storage as a destination device is to provide a consistent and seamless backup and restore experience, similar to what we have today with disk and tape. When the Backup or restore process is invoked, and the Windows Azure Blob storage is specified using the URL “device type”, the engine invokes a VDI client process that is part of this feature. The backup data is sent to the VDI client process, which sends the backup data to Windows Azure Blob storage.
As previously mentioned, the URL is much like a backup device used today, but it is not a physical device, so there are some limitations. For a full list of the supported options, see SQL Server Backup and Restore with Windows Azure Blob Storage Service.
To write a backup to Windows Azure Blob storage you must first create a Windows Azure Storage account, create a SQL Server Credential to store storage account authentication information. By using Transact-SQL or SMO you can issue backup and restore commands.
The following Transact-SQL examples illustrate creating a credential, doing a full database backup and restoring the database from the full database backup. For a complete walkthrough of creating a storage account and performing a simple restore, see Tutorial: Getting Started with SQL Server Backup and Restore to Windows Azure Blob Storage Service.
Create a Credential
The following example creates a credential that stores the Windows Azure Storage authentication information.
IF NOT EXISTS
(SELECT * FROM sys.credentials WHERE credential_identity = 'mycredential')
CREATE CREDENTIAL mycredentialWITH IDENTITY = 'mystorageaccount'
,SECRET = '<storage access key>' ;
Backing up a complete database
The following example backs up the AdventureWorks2012 database to the Windows Azure Blob storage service.
BACKUP DATABASE AdventureWorks2012
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mycredential'
,STATS = 5;
Restoring a database
To restore a full database backup, use the following steps.
RESTORE DATABASE AdventureWorks2012 FROM URL ='https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mycredential', STATS = 5
- - Note that options like STATS, COMPRESS, FORMAT etc.. can be used or omitted as for any T-SQL BACKUP command.
Best Practices /Troubleshooting
Getting Started Tutorial
Please send your feedback on the feature and ordocumentation to karaman @ Microsoft.com or guybo @ microsoft.com.
- Karthika Raman