TSQL Database Backup and Restore using Windows Azure Blob Storage

Problem

SQL Server 2012 introduces a new feature to issues backups on the Windows Azure Blob Storage service directly and restore from there when needed. But how does it work and how to get started using T-SQL commands for backup and restore operation?

Solution

SQL Server 2012 SP1 CU2 adds support for issuing backups of your database to Windows Azure Storage service and restore it from there. This feature is in addition to existing way of issuing backups to DISK and TAPE (which have been supported by SQL Server since long). In my last tip “Backup and Restore with Cloud services (Windows Azure Blob Storage Service)“, I discussed in detail about this new feature and its advantages. In this tip I am going to demonstrate how you can use T-SQL commands to leverage this new feature.

Creating Windows Azure Storage Account for backup storage

If you have not done already, you can go to Windows Azure Portal (you can find more about pricing for storage in Windows Azure here and here) and create a storage account as shown below by specifying the URL, selecting the location affinity group and by specifying whether you want to enable Geo-Replication for the stored data:

Once your Windows Azure Storage account it created, you can click on Manage Access Keys for managing primary and secondary access keys used when connecting to storage account.

can click on Manage Access Keys for managing primary and secondary access keys used when connecting to storage account.

There is a really nice tool, called Windows Azure Storage Explorer, to connect to Windows Azure Storage account for managing and exploring the content stored on the windows azure storage account. Click on Add Storage Account button and you will get this screen as shown below. Please specify the storage account name and storage access key as above and then click on Add Storage Account button.

specify the storage account name and storage access key

Once you are connected to the Windows Azure Storage account, this is how the interface should look. Here you can create a container (very much like a folder) to store backup files; in order to do that, click on New icon as shown below under Container group:

Once you are connected to the Windows Azure Storage account, this is how it should look

Please specify the name of the container and accessibility type. Its recommended to specify it as private, which restricts the access and allows users or accounts to provide the necessary information to be authenticated by Windows Azure when accessing it.

Backup a SQL Server database to a Windows Azure Blob Storage Account using T-SQL

BACKUP DATABASE and RESTORE DATABASE commands have been enhanced to have two new clauses (CREDENTIAL and URL) to it for this new feature. The CREDENTIAL clause is used to specify the SQL Server credential (a credential contains the authentication information that is required to connect to a resource outside SQL Server; in our case windows azure storage account) whereas the URL clause (Uniform Resource Locator or Universal Resource Locator) is used to specify the URL which provides a mechanism to provide Windows Azure Blob storage location and backup file name.

So before you can use the BACKUP DATABASE command to take the database backup, you need to first use the CREATE CREDENTIAL command to create a credential. First specify the azure storage account name and access key as shown below. Next use the BACKUP DATABASE command to take the database backup and store it in the Windows Azure Blob Storage using the URL and WITH CREDENTIAL clauses as shown below:

--Create a credential to connect to the windows azure storage service
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = 'mydatabasebackstore')
BEGIN
CREATE CREDENTIAL mydatabasebackstorecredential WITH IDENTITY = 'mydatabasebackstore'
,SECRET = '953VVI+PWHEpLLtKOatR/P+bJ4VavrvJLAMxgtcwHrasVRIrTq35uxNbkJze9Gvbi9vzp768ovkImeYNLeHaeA=='
END
GO
--Backup the database to the windows azure storage service - blob using URL
BACKUP DATABASE AdventureWorks2012
TO URL = 'https://mydatabasebackstore.blob.core.windows.net/adventureworks/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mydatabasebackstorecredential'
,COMPRESSION --Compress the backup
,STATS = 10 --This reports the percentage complete as of the threshold for reporting the next interval
GO

Depending on your network speed and size of the database, this process might take time. Below is a screen shot of the output after completing the above BACKUP DATABASE command:

this process might take time and this is how it should look like after completion of the above BACKUP DATABASE command execution

Now if you go to the Azure Storage Explorer, you will notice the availability of the above created backup file in the specified container as shown below:

Remember, if you have not installed SQL Server 2012 SP1 CU2 or CU4, you will encounter this exception when executing the above BACKUP DATABASE command:

Msg 155, Level 15, State 1, Line 2

‘URL’ is not a recognized Device Type option.

Msg 319, Level 15, State 1, Line 3

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an
xmlnamespaces clause or a change tracking context clause, the previous statement must be
terminated with a semicolon.

Restoring SQL Server database backup from a Windows Azure Blob Storage Account using T-SQL commands

Before we restore, let me truncate the SalesOrderDetail table with the following commands:

USE AdventureWorks2012
SELECT COUNT(*) FROM [Sales].[SalesOrderDetail]
TRUNCATE TABLE [Sales].[SalesOrderDetail]
SELECT COUNT(*) FROM [Sales].[SalesOrderDetail]

After truncation, you can notice no record exists in the SalesOrderDetail table.

Restoring a database backup from Windows Azure Blob Storage Account using T-SQL command

Now lets use this RESTORE DATABASE command to restore the backup from the Windows Azure Blog storage using the URL and WITH CREDENTIAL clauses as shown below:

USE master
RESTORE DATABASE AdventureWorks2012
FROM URL = 'https://mydatabasebackstore.blob.core.windows.net/adventureworks/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mydatabasebackstorecredential'
,MOVE 'AdventureWorks2012_Data' to 'D:\D Drive\SQL Server 2012\SampleDatabases\AdventureWorks2012_Data.mdf'
,MOVE 'AdventureWorks2012_Log' to 'D:\D Drive\SQL Server 2012\SampleDatabases\AdventureWorks2012_log.ldf'
,STATS = 10
GO

Again depending on your network speed and size of the database, the restore operation might take some time. Below is a screen shot of the output after completing the database restore:

the restore operation might take time and this is how it should look like after completion of the database restore

After the restore completes, we can verify the SalesOrderDetail table contains the data before the database backup was issued:

USE AdventureWorks2012
SELECT COUNT(*) FROM [Sales].[SalesOrderDetail]
GO

Now as you can see, we have data in the SalesOrderDetail table after the database restore is completed:

we have the data in the SalesOrderDetail after the database restore was done successfully

Next Steps

Review Windows Azure Storage Explorer.
Review SQL Server Backup and Restore with Windows Azure Blob Storage Service on msdn.
Review my last tip on this series, “Backup and Restore with Cloud services (Windows Azure Blob Storage Service)“.
Review Move an On Premise SQL Server Database to the SQL Azure Cloud tip.
Review other SQL Azure related tips
Review my previous tips

Resources

Community

Subscribe