Backup and Restore Improvements

Exclusive offer: get 50% off this eBook here
Getting Started with SQL Server 2014 Administration

Getting Started with SQL Server 2014 Administration — Save 50%

Optimize your database server to be fast, efficient, and highly secure using the brand new features of SQL Server 2014 with this book and ebook

$16.99    $8.50
by Gethyn Ellis | April 2014 | Enterprise Articles

The lines between on-premise and public cloud services have become blurred. The integration between on-premise and off-premise technologies imply that you can now easily implement a backup solution that makes use of both, thus allowing you to maintain up-to-date backups both on-site and off-site. This will allow you to implement a disaster recovery plan that meets strict service-level agreements without having to invest in a Disaster Recovery (DR) site. The DR site can be in the cloud. Depending on your environment, this can significantly reduce the cost of implementing a fully blown disaster recovery site or server room.

There are several new features in SQL Server 2014 that make this possible; they are as follows:

  • Backup to URL

  • Backup to Microsoft Azure

  • Encryption

In this article by Gethyn Ellis, the author of Getting Started with SQL Server 2014 Administration, you will look at how you can make use of the new features and integrate them into your backup and recovery plans to ensure that you maintain up-to-date off-site backups.

(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:\Backup\t3_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:


Getting Started with SQL Server 2014 Administration Optimize your database server to be fast, efficient, and highly secure using the brand new features of SQL Server 2014 with this book and ebook
Published: April 2014
eBook Price: $16.99
Book Price: $26.99
See more
Select your format and quantity:

About the Author :


Gethyn Ellis

Gethyn Ellis is a SQL Server consultant and trainer with clients in Europe and North America. His client base includes both public and private sector clients that range from large financial firms to departments of the UK government. Specialized in database administration, virtualization, and performance, he has been involved in several large projects that involve migration of large SQL Server estates to the latest version of SQL Server and also migrated that infrastructure to a virtualized platform. He is also an instructor with Learning Tree and teaches several courses on the Learning Tree SQL Server curriculum. You can follow Gethyn's blog at www.gethynellis.com.

Books From Packt


Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube
Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube

SQL Server 2012 Reporting Services Blueprints
SQL Server 2012 Reporting Services Blueprints

Microsoft SQL Server 2012 Performance Tuning Cookbook
Microsoft SQL Server 2012 Performance Tuning Cookbook

What's New in SQL Server 2012
What's New in SQL Server 2012

Instant SQL Server Analysis Services 2012 Cube Security
Instant SQL Server Analysis Services 2012 Cube Security

Reporting with Microsoft SQL Server 2012
Reporting with Microsoft SQL Server 2012

Learning SQL Server Reporting Services 2012
Learning SQL Server Reporting Services 2012

Microsoft SQL Server 2012 with Hadoop
Microsoft SQL Server 2012 with Hadoop


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software