13 February 2017
13 February 2017

SQL Clone and backups protected with TDE – part 2

My last article demonstrated that you can use SQL Clone to make a copy of a live database, even when that database is protected with Transparent Data Encryption (TDE).

However, not everyone will want to use SQL Clone against a live database, especially a production database. Fortunately, SQL Clone allows you to build an image from a backup file.

How does this work with a TDE-encrypted backup? Let’s take a look.

Building a data image from a TDE-protected database backup

The first step in building a SQL Clone database is to create an image. When I choose to create an image, I get this:

SQL Clone backup TDE 1
Figure 1

In the last article, we used a SQL Server database to create the image. In this one, let’s choose a backup. We’ll walk through the process of creating a data image from a TDE-protected backup, once creating the image on the same instance as the TDE-protected source database, and once creating the image on a separate instance.

Creating a data image on the same instance

First, we need to specify the backup location. I’ve previously added a folder, but you can easily click New Backup Location to pick a new one. This is a folder to which the SQL Clone Agent service has access.

Once I’ve specified the location, I need to specify a SQL Server instance, as SQL Clone needs to create a temporary database on this instance in order to restore the backup. In essence, the process of restoring the backup is what creates the data image. I’ll choose my SQL2016 named instance, which also hosts the source database.

The last item to specify is the backup file(s). Since a backup can span multiple files, I would need all the files listed here.

SQL Clone backup TDE 2
Figure 2

Now the form is filled, I click NEXT. I pick a destination for the image, which is a shared location that all of my SQL Clone agents can access.

SQL Clone backup TDE 4

Figure 3
Once I do this, I give the image a name and review my choices.

SQL Clone backup TDE 4
Figure 4

Click CREATE IMAGE and the image creation runs. As you can see, SQL Clone creates a temporary database on my instance in order to perform the restore:

SQL Clone backup TDE 5
Figure 5

The process completes, and I have an image. Adding a clone follows the same steps as described in the previous article, so I won’t go over them again here.

Creating a data image on a separate instance

What if we need to create a data image from a TDE-protected backup, but use a different instance for the restore process? In this case, I need to add a new backup location and associate this with a new instance.

To do this, I’ll click New Backup Location when I start to create a new image. I fill out the location and instance. I copied the same TDE-protected backup file that we used previously to this location.

SQL Clone backup TDE
Figure 6

Now we can use the backup file to create the image. I’ll select the same image location, and then enter the image name. My settings look the same as before, though this restore is using a different instance.

SQL Clone backup TDE 7
Figure 7

On this instance, however, I don’t have the certificate that protects my TDE database.

SQL Clone backup TDE 8
Figure 8

I would assume this means the restore can’t take place and therefore that the image won’t be created. So, what happens when I click CREATE IMAGE? As expected, I get a server thumbprint error.

SQL Clone backup TDE 9
Figure 9

This is expected behavior when restoring a TDE-encrypted backup. I need the server certificate in order to decrypt the DEK and then read my encrypted data. I’ll restore the certificate as I did in the first article. Once I do that, I’ll see my server certificate in master.

SQL Clone backup TDE 10
Figure 10

I can restart my image creation process, and this time it runs.

SQL Clone backup TDE 11
Figure 11

No error, my image is created, and I see it in my list in the dashboard.

SQL Clone backup TDE 12
Figure 12

I can then create clones from this new image, just as I did in the first article.

SQL Clone and TDE ‘just works’

TDE is a great feature that provides a layer of security. It’s fairly simple, and easy to implement. It is also easy to move TDE databases across servers, if you understand how to move a certificate.

As you can see, SQL Clone builds on the SQL Server platform, and integrates nicely with TDE, provided you have the certificates created on the instances where you need to build images or SQL Clone databases.

Tools in this post

SQL Clone

Clone SQL Server databases in seconds and save up to 99% disk space.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Hub

How to Test SQL Server Functions and Procedures using SQL Prompt

When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a bat...

Also in Product learning

Baselining a ReadyRoll project from an existing database

Deploying schema changes to SQL Server databases can be tricky when you’d like to automate parts of your workflow. For instance, how do you go about version controlling your schema changes? In appli...

Also in SQL Clone

NEW - Create, protect and manage non-production databases with SQL Provision

SQL Provision launches today. It allows users to create copies of SQL Server databases in seconds, using a fraction of disk space, and mask any sensitive data to help address data privacy and protect...

Also about Database DevOps

Bringing DevOps to the database. Part 2: Continuous delivery

In part 1 of Bringing DevOps to the database, we saw how DevOps thinking is moving from the application to the database. By encouraging collaboration not competition between developers and Database Ad...

Also about SQL Clone

How to automatically provision sanitized data using SQL Clone, Data Masker and PowerShell

National and regional legislation, as well industry standards, dictate what an organization can and can't do with sensitive data, as well as how it needs to be handled, audited and protected. In addit...