From Cumulative Update 2 for SQL Server 2012 Service Pack 1, SQL Server provides more storage option for backup and restore. SQL Server backups can be storage in Windows Azure Storage. You can backup and restore to/from Windows Azure Blob storage by using Transact-SQL and SMO(Server Management Objects). This enhancement can be used on an on-premise SQL Server. But, it will be a great backup storage of SQL Server on VM(Windows Azure Virtual Machine). Let’s see how to backup and restore to/from Windows Azure Blob storage.
To use this feature, you need to install cumulative update 2 for SQL Server 2012 Service Pack 1(SP1). You can download cumulative update 2 for SQL Server 2012 Service Pack 1(SP1) from here.
You need to have a Windows Azure account.If you do not have one, you can use Windows Azure 90 day free trial from here.
To backup database to Windows azure blob storage, you should create your Windows Azure storage account.
Next, you need to create a container in storage account. A container provides a grouping of a set of blobs. All blobs must be in a container. An account can contain an unlimited number of containers. A container can store an unlimited number of blobs.
First, you need to create a credential that store the Windows Azure storage authentication information in SQL Server.
Connect to SQL Server using SQL Server management studio and open a new query window.
The following Transact-SQL code snippet shows creating a credential of Windows Azure storage authentication. Copy the following code to new query windows.
CREATE CREDENTIAL mycredentialWITH IDENTITY = 'mystorageaccount',SECRET = '<storage access key>';
Back to Windows Azure Management Portal, click DASHBOARD button of your storage account.
Manage Access Keys windows will pop up.
Copy the name of storage account(pfestorageaccount) and paste it to 'mystorageaccount'.Copy value of PRIMARY ACCESS KEY or SECONDARY ACCESS KEY and paste it to '<storage access key>'.
Your Transact-SQL will be
Execute t-sql statement.
You can verify ‘mycredential’ credential is created successfully by using the following statement.
After creating a credential, you can back up your database to the Windows Azure blob storage using your credential. The following example backs up the AdventureWorks2012 database to the Windows Azure Blob Storage service:
BACKUP DATABASE AdventureWorks2012TO URL = 'https://pfestorageaccount.blob.core.windows.net/sqlbackup/AdventureWorks2012.bak'WITH CREDENTIAL = 'mycredential',STATS = 5;GO
If you have any 3rd party management tool of Windows Azure Storage, you can check the backup file using that.
To restore a full database backup, execute the below Transact-SQL:
RESTORE DATABASE AdventureWorks2012FROM URL = 'https://pfestorageaccount.blob.core.windows.net/sqlbackup/AdventureWorks2012.bak'WITH CREDENTIAL = 'mycredential',STATS = 5;GO
There is more detailed steps on MSDN library. Please refer to this tutorial.