Back up SQL Server databases to Windows Azure storage

Author by Concurrency Blog

One of the key strategies I stress for SQL Server is storing multiple copies of your backup files. If you only have one copy, what would happen if the storage it was on became corrupted? Traditional methods of retaining multiple copies may include backing up to tape and having that stored offsite, or copying the files to a location at a second data center. Both of these options can come with significant costs, as they involve maintaining equipment at multiple locations or using third parties. A more cost-effective method can be using Windows Azure for storage. Using Azure means you won't need to find a third party to pick up and drop off tapes, or manage a second data center. There are a couple of ways to back up SQL Server databases to Azure storage. You could store the backups in a local location, then copy those files to Azure (using a tool such as AzCopy). Another option, native to SQL Server since SQL Server 2012, is backing up directly from the database to Azure blob storage. This option has pros and cons. It allows for offsite storage, it can be scheduled like any other SQL Server backup job, and you can store blobs up to 1 TB in size. However, backing up to Azure does take longer than backing up to local storage, a GUI option is not present in 2012, and some options - such as striping across multiple files - are not available. Interested? Here's a simple 3-step process that can be followed to back up your SQL Server databases to Azure. First, in your Azure account, create a storage account, and from the Dashboard, copy the name and Primary Access Key. Create a container in the account, setting access to private, and copy the URL. Second, in your SQL Server instance, created a Credential for the container. The syntax is:
CREATE CREDENTIAL CredentialName 
WITH IDENTITY= 'StorageAccount', 
SECRET = 'PrimaryAccessKey';
Third, back up the database using URL instead of file. The syntax is:
BACKUP DATABASE DatabaseName 
TO URL = 'containerURL/BackupName.bak' 
WITH CREDENTIAL = 'CredentialName';
GO
As an example, if I had a storage account named ConcurrencyStorage, a container named SQLBackups, a credential named AzureBackups, and I wanted to back up my AdventureWorks database, my command would look like:
BACKUP DATABASE AdventureWorks
TO URL = 'https://ConcurrencyStorage.blob.core.windows.net/SQLBackups/AdventureWorksFull.bak' 
WITH CREDENTIAL = 'AzureBackups';
GO
This will be slower than backing up to local storage, and there are maximum file sizes. This isn't an option for all SQL Server database, but it is an option to give you more peace of mind and better disaster recovery! Want to give it a try? Register for an Azure FREE trial!
Author

Concurrency Blog

The latest about Concurrency