13 September 2017
13 September 2017

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 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

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

You may also like

  • Article

    A behind the scenes glimpse of SQL Clone

    It has always been a difficult task to provision development and test environments so that they reflect as closely as possible what’s present in production. With the rise of containerization and Infrastructure as Code (IaC) technologies, some parts of this are becoming much easier. We can automate the process of spinning up and configuring new virtual

  • Webinar

    Does your current provisioning process meet regulatory requirements?

    DevOps practices, applied to the database, aim to allow organizations to deploy databases at the frequency they need, without introducing an unrealistic administration burden, and without compromising that organization’s compliance with data privacy or security regulations. In this webinar, Product Manager Richard Macaskill explains how to incorporate Redgate’s new SQL Provision into your Database DevOps

  • Article

    Backup and restore of a SQL Clone

    With SQL Clone, I can create multiple copies of my database, for testing, very quickly. First, I create an image of the source database, and then from that image I can create multiple clones. Each copy, or clone, appears to SQL Server as a normal database, and yet occupies a fraction of the disk space

  • Webinar

    How to treat compliance in DevOps as serious business

    We hosted this webinar to discuss how, by embedding ‘Privacy by Design’ in your Database DevOps practices, you can introduce a framework that builds a bridge between fast AND secure software development. We arm you with practical tips on how to discover, classify, protect and monitor your SQL Server estate, and therefore ensure that your Database DevOps practices are secure

  • Webinar

    Keep sensitive data secure as it moves through your SQL Server estate

    In this webinar, Chris Unwin, will show you how to implement a consistent and repeatable process designed with privacy in mind up front. You will learn how to create an accurate picture of your SQL Server estate and steps you can take to ensure the ongoing protection of sensitive data.

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant