• SQL Clone
• Compliant Database Provisioning
• Development and Testing with Clones

Database provisioning from backups using SQL Clone

Updated September 2017

In a previous article, Getting Started with SQL Clone, I described how SQL Clone could remove much of the administrative burden from the database provisioning process, the advantages of a new, lightweight process to spin up ‘real databases’ for use in development and test work, and finally how to produce images and then clones, starting from a live database.

This article offers a brief, step-by-step guide to the alternative approach of creating an image from a database full backup, or from a full backup plus differential backup. This post was written on SQL Clone v 3.2.

Why clone from backups?

Creating an image directly from a live database is a good approach if, for example, you’ve created a database specifically for this purpose, such as by building the latest version of a production database from a build script in source control, and then importing standard data sets, or by generating ‘production-like’ data using a data generation tool.

This approach is often the only option for setting up development and test databases, if regulations prohibit you from using or distributing copies of production data.

If there are no significant data sensitivity issues, then in theory you could create an image directly from the production database. However, not many DBAs will allow third party tools to have direct access to the production environment, so in practice it’s likely that you’ll be provisioning databases from the latest database backups.

How SQL Clone creates an image from a database backup

When we create an image with SQL Clone, we’re creating a Virtual Hard Disk (VHD) to which SQL Clone captures a page-by-page copy of the database. SQL Clone now supports using uses the VHDX format (.vhdx) to allow cloning databases up to 64TB.

When starting from a database backup, SQL Clone still needs to extract the MDF and LDF files from this backup to create the image. In effect, we do this by restoring the backup directly into the VHD, to create the image, rather than needing to restore the backup(s) first, and then use the Volume Shadow Copy Service to create the image.

Nevertheless, when using a database backup as the image source, we still need to specify an associated SQL Server instance. To convince SQL Server that it’s just performing a ‘normal’ database restore, SQL Clone must create a temporary database on the specified instance. In fact, though, the temporary database doesn’t consume any additional disk space; it is essentially what we might term a pass-through restore.

Figure 1 depicts this process. SQL Clone creates the initial VHD at the image location, such as a remote file share, then creates a mount point for this VHD so that the operating system on a SQL Server instance can access it, and finally restores the backup directly into the remote VHD to create the image.

Figure 1

Having created the image, many clones, either on the same server or on network servers, can reference this image, independently.

Creating an image from a full database backup using the UI

For this example, we’ll create an image from a database full backup of a Customers database, on a SQL Server 2016 instance, which I filled with sample data using SQL Data Generator.

Open the SQL Clone web application, which is called SQL Clone Server. To create a new image from a database backup, select the Backup option for the source type.

Figure 2

At the next screen, we need to add both a SQL Server instance, for the ‘pass-through’ database restore operation, as described earlier, and the backup location.

I’ve chosen a SQL Server 2016 instance, and SQL Clone Server has performed a check that it can access that server using the account specified during the agent install process, as shown in Figure 3.

Figure 3

In the bottom half of the same screen, we enter to the path to the backup file, or files. In this example, as shown in Figure 4, I’m just using a full backup, and the backup folder is a local folder on the SQL Server 2016 instance. You can choose any network folder, by entering its network UNC path, provided the SQL Server account can access this location to perform the restore.

Figure 4

SQL Clone Server validates the path provided, and then the next step is to enter the image location. Again, you can select a previously-registered location, or create a new one. Since I’ve already set up a shared network folder that can be accessed by all SQL Server instances to which I need to deploy clones, I simply select that location from the dropdown.

Figure 5

SQL Clone validates the location, and the final step is to review all the details and enter an image name, which will likely include a date or a database version, or some other identifying characteristic.

Figure 6

Click Create Image, and if all goes well, you’ll see a progress screen, as SQL Clone creates the image. Remember that the image creation process will take about the same length of time as a normal database backup or restore. If it’s a big database, it will take a while, but it’s a one-time cost. Having created the image, creating clones from this image will take seconds.

If you switch to SSMS, during image creation, you should see in the Object Explorer that a restore operation is in progress.

Figure 7

Once it completes, you’ll see a summary screen like the one shown in Figure 8.

Figure 8

The image size, as reported by SQL Clone Server, is about 359 MB; and we can confirm this from the size of the data image, at the image location, as shown in Figure 9.

Figure 9

SQL Clone appends the image ID (in this case 10005, padded out to 00010005) and some random characters to prevent filename collisions, but its name in SQL Clone web Server, and in PowerShell scripts, is just customers_2_v2.2.

SQL Clone appears to report the size of the data image as being substantially larger than the original database (255 MB), but in fact this is simply due to the dynamic VHD resizing mechanism; the image is just a straight copy of the data and log files.

Creating an image from a full plus differential database backups using the UI

SQL Clone can also create an image from striped backups, or from a database full backup plus database differential backup. We’ll walk through an example of the latter. I’ve added a few rows to a table in my Customers database, and captured a differential backup.

A differential backup always contains a copy of every page that has changed since since the most recent full backup, and so is tied to that specific full backup. In other words, we always need either just the full backup, or the differential backup and its ‘base’ full backup, as shown in Figure 10.

Figure 10

SQL Clone Server will validate the backups, and then you’ll proceed to the next step, which is to define the image location, as we saw previously. In this case, I’ve simply created a new image, in the same location, called Customers_2_v2.2.1.

Figure 11

Creating images from backups using PowerShell

If you navigate to the Settings | PowerShell section in SQL Clone Server, you’ll find the download link for the cmdlets, and some simple scripted examples of creating images and deploying clones. If you’ve just upgraded from an older version of SQL Clone, you’ll still need to download and install the new PowerShell cmdlets.

Figure 12

Listing 1 adapts the image creation script provided there, to create another image from the full database backup, auto-appending the image name with the current date and time.

Listing 1

Figure 13 shows the resulting image in SQL Clone Server dashboard.

Figure 13

To create an image from a full plus differential backup, simply supply to the BackupFileName parameter the two backup filenames, comma delimited, as follows:

 -BackupFileName @('C:\MyDatabaseBackups\Customers\Customers_2_Full.bak', 'C:\MyDatabaseBackups\Customers\Customers_2_Diff.bak') `

Deploying clones from images

From our image, we can now create and deploy clones very quickly. This part of the process is described elsewhere, so I won’t repeat the details here. For example, my Getting Started with SQL Clone article shows how to do this through SQL Clone Server UI, and you can also deploy clones simply SQL Clone’s PowerShell cmdlets.

Listing 2 adapts the script supplied in the Settings | PowerShell section of the UI to deploy a clone to the default instance on my SQL Server machine.

Listing 2

Note that the image, machine and instance names should be the names as registered in SQL Clone Server. Figure 14 shows the new clone in SQL Clone server dashboard. Notice that it is only about 48 MB in size.

Figure 14

Other supported database backups

SQL Clone fully supports creating images from full database backups and differential database backups (including striped backups). It does not yet support creating an image from a combination of full (and optionally differential) plus transaction log backups.

SQL Clone supports creating images from databases that contain Filestream data files. For example, we can create an image from a backup of the WideWorldImporters database. SQL Clone also supports creating the image from a backup of a database protected by Transparent Data Encryption.

Summary

Often DBAs can’t allow direct access to the production server, for tools such as SQL Clone. This article demonstrated the very basics of creating images, starting from the latest database full backup, or from the full backup plus differential backup.

Find out more about how SQL Clone makes database provisioning easy with a 14-day fully functional free trial.

Tools in this post

SQL Clone

Create SQL Server database copies in an instant

Find out more

SQL Provision

Provision virtualized clones of databases in seconds, with sensitive data shielded

Find out more