10 min read

(For more resources related to this topic, see here.)

Database backups to a URL and Microsoft Azure Storage

The ability to backup to a URL was introduced in SQL Server 2012 Service Pack 1 cumulative update package 2. Prior to this, if you wanted to backup to a URL in SQL Server 2012, you needed to use Transact-SQL or PowerShell. SQL Server 2014 has integrated this option into Management Studio too.

The reason for allowing backups to a URL is to allow you to integrate your SQL Server backups with cloud-based storage and store your backups in Microsoft Azure. By being able to create a backup there, you can keep database backups of your on-premise database in Microsoft Azure. This makes your backups safer and protected in the event that your main site is lost to a disaster as your backups are stored offsite. This can avoid the need for an actual disaster recovery site.

In order to create a backup to Microsoft Azure Storage, you need a storage account and a storage container.

From a SQL Server perspective, you will require a URL, which will specify a Uniform Resource Identifier (URI) to a unique backup file in Microsoft Cloud. It is the URL that provides the location for the backup and the backup filename. The URL will need to point to a blob, not just a container. If it does not exist, then it is created. However, if a backup file exists, then the backup will fail. This is unless the WITH FORMAT command is specified, which like in older versions of SQL Server allows the backup to overwrite the existing backup with the new one that you wish to create.

You will also need to create a SQL Server credential to allow the SQL Server to authenticate with Microsoft Azure Storage. This credential will store the name of the storage account and also the access key. The WITH CREDENTIAL statement must be used when issuing the backup or restore commands.

There are some limitations you need to consider when backing up your database to a URL and making use of Microsoft Azure Storage to store your database backups:

  • Maximum backup size of 1 TB (Terabyte).

  • Cannot be combined with backup devices.

  • Cannot append to existing backups—in SQL Server, you can have more than one backup stored in a file. When taking a backup to a URL, the ratio should be of one backup to one file.

  • You cannot backup to multiple blobs. In a normal SQL Server backup, you can stripe it across multiple files. You cannot do this with a backup to a URL on Microsoft Azure.

There are some limitations you need to consider when backing up to the Microsoft Azure Storage; you can find more information on this at http://msdn.microsoft.com/en-us/library/dn435916(v=sql.120).aspx#backuptaskssms.

For the purposes of this exercise, I have created a new container on my Microsoft Azure Storage account called sqlbackup.

With the storage account container, you will now take the backup to a URL. As part of this process, you will create a credential using your Microsoft Azure publishing profile. This is slightly different to the process we just discussed, but you can download this profile from Microsoft Azure. Once you have your publishing profile, you can follow the steps explained in the following section.

Backing up a SQL Server database to a URL

You can use Management Studio’s backup task to initiate the backup. In order to do this, you need to start Management Studio and connect to your local SQL Server instance. You will notice that I have a database called T3, and it is this database that I will be backing up to the URL as follows:

  1. Right-click on the database you want to back up and navigate to Tasks | Backup. This will start the backup task wizard for you.

  2. On the General page, you should change the backup destination from Disk to URL. Making this change will enable all the other options needed for taking a backup to a URL.

  3. You will need to provide a filename for your backup, then create the SQL Server credential you want to use to authenticate on the Windows Azure Storage container.

  4. Click on the Create Credential button to open the Create credential dialog box. There is an option to use your publishing profile, so click on the Browse button and select the publishing profile that you downloaded from the Microsoft Azure web portal. Once you have selected your publishing profile, it will prepopulate the credential name, management certificate, and subscription ID fields for you. Choose the appropriate Storage Account for your backups.

  5. Following this, you should then click on Create to create the credential.

  6. You will need to specify the Windows Azure Storage container to use for the backup. In this case, I entered sqlbackup. When you have finished, your General page should look like what is shown in the following screenshot:

  7. Following this, click on OK and the backup should run.

  8. If you want to use Transact-SQL, instead of Management Studio, to take the backup, the code would look like this:

    BACKUP DATABASE [T3] TO  URL 
= N'https://gresqlstorage.blob.core.windows.net/sqlbackup/t3.bak' 
WITH  CREDENTIAL = N'AzureCredential' , NOFORMAT, NOINIT,  
NAME = N'T3-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This is a normal backup database statement, as it has always been, but it specifies a URL and a credential to use to take the backup as well.

Restoring a backup stored on Windows Azure Storage

In this section, you will learn how to restore a database using the backup you have stored on Windows Azure Storage:

  1. To carry out the restore, connect to your local instance of SQL Server in Management Studio, right-click on the databases folder, and choose the Restore database option.

  2. This will open the database restore pages. In the Source section of the General page, select the Device option, click on the dropdown and change the backup media type to URL, and click on Add.

  3. In the next screen, you have to specify the Windows Azure Storage account connection information. You will need to choose the storage account to connect to and specify an access key to allow SQL Server to connect to Microsoft Azure. You can get this from the Storage section of the Microsoft Azure portal. After this, you will need to specify a credential to use. In this case, I will use the credential that was created when I took the backup earlier.

  4. Click on Connect to connect to Microsoft Azure. You will then need to chose the backup to restore from. In this case, I’ll use the backup of the T3 database that was created in the preceding section.

You can then complete the restore options as you would do with a local backup.

In this case, the database has been called T3_cloud, mainly for reference so that it can be easily identified. If you want to restore the existing database, you need to use the WITH REPLACE command in the restore statement. The restore statement would look like this:

RESTORE DATABASE t3 FROM URL =
' https://gresqlstorage.blob.core.windows.net/sqlbackup/t3.bak '
WITH CREDENTIAL = ' N'AzureCredential' '
,REPLACE
      ,STATS = 5

When the restore has been completed, you will have a new copy of the database on the local SQL Server instance.

SQL Server Managed Backup to Microsoft Azure

Building on the ability to take a backup of a SQL Server database to a URL and Microsoft Azure Storage, you can now set up Managed Backups of your SQL Server databases to Microsoft Azure. It allows you to automate your database backups to the Microsoft Azure Storage. All database administrators appreciate automation, as it frees their time to focus on other projects. So, this feature will be useful to you. It’s fully customizable, and you can build your backup strategy around the transaction workload of your database and set a retention policy.

Configuring SQL Server-managed backups to Microsoft Azure

In order to set up and configure Managed Backups in SQL Server 2014, a new stored procedure has been introduced to configure Managed Backups on a specific database. The stored procedure is called smart_admin.sp_set_db_backup. The syntax for the stored procedure is as follows:

EXEC smart_admin.sp_set_db_backup  
     [@database_name = ] 'database name'
    ,[@enable_backup = ] { 0 | 1}
    ,[@storage_url = ] 'storage url'
    ,[@retention_days = ] 'retention_period_in_days'
    ,[@credential_name = ] 'sql_credential_name'
    ,[@encryption_algorithm] 'name of the encryption algorithm'
    ,[@encryptor_type] {'CERTIFICATE' | 'ASYMMETRIC_KEY'}
    ,[@encryptor_name] 'name of the certificate or asymmetric key'

This stored procedure will be used to set up Managed Backups on the T3 database. The SQL Server Agent will need to be running for this to work. In my case, I executed the following code to enable Managed Backups on my T3 database:

Use msdb;
GO 
EXEC smart_admin.sp_set_db_backup @database_name='T3'
,@enable_backup=1
,@storage_url = 'https://gresqlstorage.blob.core.windows.net/'
,@retention_days=5
,@credential_name='AzureCredential'
,@encryption_algorithm =NO_ENCRYPTION

To view the Managed Backup information, you can run the following query:

Use msdb
GO
SELECT * FROM smart_admin.fn_backup_db_config('T3')

The results should look like this:

To disable the Managed Backup, you can use the smart_admin.sp_set_db_backup procedure to disable it:

Use msdb;
GO 
EXEC smart_admin.sp_set_db_backup @database_name='T3'
,@enable_backup=0

Encryption

For the first time in SQL Server, you can encrypt your backups using the native SQL Server backup tool. In SQL Server 2014, the backup tool supports several encryption algorithms, including AES 128, AES 192, AES 256, and Triple DES. You will need a certificate or an asymmetric key when taking encrypted backups. Obviously, there are a number of benefits to encrypting your SQL Server database backups, including securing the data in the database. This can also be very useful if you are using transparent data encryption (TDE) to protect your database’s data files. Encryption is also supported using SQL Server Managed Backup to Microsoft Azure.

Creating an encrypted backup

To create an encrypted SQL Server backup, there are a few prerequisites that you need to ensure are set up on the SQL Server.

Creating a database master key for the master database

Creating the database master key is important because it is used to protect the private key certificate and the asymmetric keys that are stored in the master database, which will be used to encrypt the SQL Server backup. The following Transact-SQL will create a database master key for the master database:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$W0rd';
GO

In this example, a simple password has been used. In a production environment, it would be advisable to create a master key with a more secure password.

Creating a certificate or asymmetric key

The backup encryption process will need to make use of a certificate or asymmetric key to be able to take the backup. The following code creates a certificate that can be used to back up your databases using encryption:

Use Master
GO
CREATE CERTIFICATE T3DBBackupCertificate
   WITH SUBJECT = 'T3 Backup Encryption Certificate';
GO

Now you can take an encrypted backup of the database.

Creating an encrypted database backup

You can now take an encrypted backup of your databases. The following Transact-SQL statements back up the T3 database using the certificate you created in the preceding section:

BACKUP DATABASE t3
TO DISK = N'C:Backupt3_enc.bak'
WITH
  COMPRESSION,
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = T3DBBackupCertificate
   ),
  STATS = 10
GO

This is a local backup; it’s located in the C:backup folder, and the encryption algorithm used is AES_256.

Summary

This article has shown some of the new backup features of SQL Server 2014. The ability to backup to Microsoft Azure Storage means that you can implement a robust backup and restore strategy at a relatively lower cost.

Resources for Article:

Further resources on this subject:

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here