Database Provisioning From Backups Using SQL Clone

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 data images and then clones, starting from a live database.

This article offers a brief, step-by-step guide to the alternative approach of creating a data image from a database full backup. It uses SQL Clone v1.0.

Why Clone From a Backup?

You can use SQL Clone to create a data image directly from a live database. This 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 source control, then importing standard data sets, or generating ‘production like’ data using a data generation tool.

The above approach might be your only option if regulations prohibit you from using or distributing copies of production data. However, if there are no real data sensitivity issues, then you might be able to create a data image directly from the production database. However, even then, not many DBAs will allow third party tools direct access to the production environment, so in practice, it more likely means provisioning databases from the latest database backups.

Create a Data Image From a Database Backup

When we create the data image, we’re creating a Virtual Hard Disk (VHD) to which SQL Clone captures a page-by-page copy of the database. Many clones, on the same server or on network servers, can reference this data image, independently.

SQL Clone creates the initial VHD at the data image location (such as a remote file share), creates a mount point for this VHD so that the operating system on a SQL Server instance can access it, then restores the backup into the VHD to create the data image.

When specifying a database backup as the data image source, we need to specify an associated SQL Server instance. In order to convince SQL Server that it’s just performing a “normal” database restore, SQL Clone has to 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”. SQL Server is really restoring directly into the remote VHD, to create the data image.

Figure 1

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

In SQL Clone Server, create a new image, and select the option to create the image from a Backup.

Figure 2

The first task is to specify the backup location. We can pick an existing location from the dropdown or add a new one.

Figure 3

We’ll add a new backup location. The account that SQL Server runs as needs to be able to access this backup location in order to perform the restore and create the image from the backup. As described previously, this means we need to specify a SQL Server instance so that SQL Clone can create a temporary database.

In this example, as shown in Figure 4, the backup folder is a local folder on the specified SQL Server 2016 instance, but you can specify a network folder, to which SQL Server has access, by providing the full network UNC path to the network share.

Figure 4

Note that, currently, the path to the backup folder must be unique, since it identifies the associated SQL Server instance for the restore (see the later PowerShell scripts).

SQL Clone Server will validate the path provided, and then the next step is to specify the name of the backup file.

Figure 5

Finally, we need to specify the data image destination, and again you can select a previously-registered location, or create a new one. In this case, 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.

Figure 6

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

Figure 7

Click Create Image, and if you switch quickly to SSMS, you should see in the Object Explorer that a restore operation is in progress.

Figure 8

The image creation should take about as long as a normal database restore. SQL Clone supports creating the image from a backup of a database protected by Transparent Data Encryption.

Assuming it completes successfully, you’ll see a summary screen similar to the one shown in Figure 9.

Figure 9

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 data image location, as shown in Figure 10.

Figure 10

SQL Clone appends the clone ID (6, padded out to 000000006) and some random characters to prevent filename collisions, but the name in SQL Clone Server (and in PowerShell scripts) is just customers_2_v1.12.

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.

SQL Clone v1.0 supports only data and log files, so if you try to create an image from a database that has data in other types of files, such as Filestream data files, you’ll see an error message in attempting to create the image, such as the one in Figure 11, when attempting to create an image from the WideWorldImporters database.

Figure 11

Deploying Clones Using PowerShell cmdlets

From our data image, we can now create and deploy clones; my previous article shows how to do this through SQL Clone Server, but the alternative is to automate image creation, and clone deployment using SQL Clone’s PowerShell cmdlets.

If you navigate 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.

Figure 12

Listing 1 simply adapts the image creation script for the example in this article. Note this script only works if you’ve previously registered the $backupLocation via the UI, because this associates that location with a SQL Server instance, for the restore operation.

Connect-SqlClone -ServerUrl 'http://MySQLSQLCloneServer:14145'
$backupLocation = Get-SqlCloneBackupLocation -Path 'C:\MyDatabaseBackups\Customers'
$imageDestination = Get-SqlCloneImageLocation -Path '\\MySharedImageServer\SQLClone'

$imageOperation = New-SqlCloneImage -Name "Customers_2-$((get-date).ToString("yyyyMMddHHmmss"))" `
    -BackupLocation $backupLocation `
    -BackupFileName @('Customers_2_Full.bak') `
    -Destination $imageDestination

$imageOperation | Wait-SqlCloneOperation

Listing 1

On this occasion, we auto-append the image name with the current date and time. Figure 13 shows the resulting data image in SQL Clone Server dashboard.

Figure 13

Listing 2 shows the next step, which is to deploy a clone, called Customers_2-clone1 to one or more target servers, in this case just to the default instance on my SQL 2016 server. Note that the image, machine and instance names should be the names as registered in SQL Clone Server.

# now create the clone
Connect-SqlClone -ServerUrl 'http://MySQLCloneServer:14145'
$image = Get-SqlCloneImage -Name 'customers_2-20170302151707'
$sqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName TONY-SQL2016 -InstanceName ''
$image | New-SqlClone -Name 'Customers_2-clone1' -Location $sqlServerInstance | Wait-SqlCloneOperation

Listing 2

Figure 14 shows the new clone in SQL Clone server dashboard. Notice that it is only about 42 MB in size.

Figure 14

By default, currently, the SQL Clone agent creates the clones at %localappdata%\Red Gate\SQL Clone, although this may change in future, and it’s also possible to use a different location using the Advanced Settings section of the Clone Agent installer.

Navigate to this location (%localappdata% refers to the location for the SQL Clone agent user account), and you’ll find a folder holding each of the clones created on that machine.

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 data images, and then clones, starting from the latest database full backup. As of SQL Clone v1.0, we can start from full backups only; there isn’t yet support to start from full backup, plus latest differential backup plus logs.