cmatskas

Backing up SQL Server to Azure Storage account using T-SQL

Christos Matskas -

One of the cool things of SQL Server 2012, is the ability to backup any database to Azure. This frees DBA’s from having to maintain tapes for offsite backups. Think of it as your Disaster Recovery on the cheap (and easy) The setup is incredibly simple and I would urge you to take advantage of this feature as soon as possible. If you don’t have an Azure account, you can get one here

In order to be able to backup to Azure, you need to configure a credential to store the Windows Azure Storage authentication details. You can do this either using T-SQL or Powershell. In this example, we will use T-SQL

Create the SQL Credential###

To create a new SQL Server credential, in SQL Server Management Studio, open a new Query window and paste the following:

IF NOT EXISTS
(SELECT * FROM sys.credentials 
WHERE credential_identity = 'mycredential')
CREATE CREDENTIAL mycredential WITH IDENTITY = 'mystorageaccount'
,SECRET = '<storage access key>' ;

Make sure you replace the following on the script:

  • ‘mycredential’ with your sql credential you wish to create
  • ‘mystorageaccount’ with your azure storage account
  • ‘ with the corresponding storage account key

The Azure credentials can be found in the Azure Portal. Click on STORAGE on the left and select the storage account you wish to use. Under the DASHBOARD tab, at the bottom toolbar you will find the Manage Access Keys link. Click on it and you will be presented with the following window:

You can use any of the two keys for this task, but note that if you ever have to regenerate the keys for the storage account, you will have to update your SQL Credentials to use the new keys.

Next you may want to create your own container under the Storage account to hold your backups. Let’s go with Backups. Still within the Azure Portal Storage Account, select the CONTAINERS tab at the top and click on the (+) Add button at the bottom toolbar. Enter “backups” for the name and keep the Access “Private”.

It should take a moment to create. Now copy the URL value as we will need it for the next step.

Perform a backup using the Azure storage container###

Open a SQL Query and paste the following command:

BACKUP DATABASE TestDB 
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/TestDB_Backup.bak' 
      WITH CREDENTIAL = 'mycredential' 
     ,COMPRESSION
     ,STATS = 5;
GO

Ensure to replace the long url with your container’s url that you copied on the previous step and replace the ‘mycredential’ with the SQL Credential you created on the previous step. Depending on the size of the database, the backup can take between a few seconds to minutes. It is very important that you define the Compression option as otherwise you will face a loooong wait, especially for larger databases.

Perform a restore using a file stored in Azure storage account###

The restore is just the reverse of what we did on the previous step. Open a SQL Query and paste the following command:

RESTORE DATABASE TestDB FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/TestDB_Backup.bak'
WITH CREDENTIAL = 'mycredential'

Once again, remember to replace the url and credentials with your values and you are good to go. A more comprehensive write up on SQL Backups in Azure can be found here

Happy coding…