Documentation

How to create an image from database backups using SQL Clone

We can create an image from a full database backup, or from a full database backup plus differential database backup. SQL Clone can also create an image from striped backups. When creating an image from a database backup, SQL Clone simply extracts the MDF and LDF files from the backup to create the image.

Following are the basic actions that SQL Clone performs 'behind the scenes':

• Create an initial, small Virtual Hard Disk (VHD), at the image location
• Create a mount point to this VHD so that the local operating system of the SQL Server instance hosting the source database can access it
• Restore the backup directly into the remote VHD to create the image

When using a database backup as the image source, you will 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. The temporary database doesn't consume any additional disk space; it is what we might term a pass-through restore.

Specify the Backup source

From the SQL Clone web client:

1. Choose Create image and then choose Backup as the Source Type.
2. Select the SQL Server instance for the temporary "pass through" restore.
   1. SQL Clone will verify that it can connect using the credentials it has stored for that instance
3. Choose your Backup type – a single full database backup file, or to a set of files (for differential or striped backups).
   1. SQL Clone does not yet support the inclusion of transaction log backups in the source backup set. It does support databases that contain Filestream data files, as well as backups of databases protected by Transparent Data Encryption.
4. Specify the full path to each backup file – a differential backup contains a copy of every page that has changed since the most recent full backup, so you will need either just the full backup, or the differential backup and its 'base' full backup.

Would you like to modify the image?

5. Upload any files that will modify the image. SQL Clone will apply them as part of the image creation process. This might include:
   1. Custom data masking scripts, or data masking sets created in Data Masker for SQL Server, to sanitize personal and sensitive information
   2. Scripts to change configuration settings or amend permissions, as required to produce a working clone in the target environment.

Image Destination

6. Provide a UNC path to the image destination
   1. If you've previously established an image location, it will appear in the dropdown, otherwise just type in the address.

Review and create image

7. Give the image a meaningful name – this might include the date the image was taken, or perhaps the current build version of the database.
8. Click Create Image – the image creation process will start, and you'll see a progress screen
   1. Creating an image takes a full byte-by-byte copy of the database and will take about as long as a database backup or restore operation to the same network location. However, it's a one-time cost and creating clones from this image will take seconds.
   2. When the image creation process completes, you'll see a screen confirming the image was created, and showing its size.
      1. You can verify the image size by inspecting the VHD file, at the image location.
      2. SQL Clone appends the image ID with some random characters to prevent filename collisions, but its name in SQL Clone Server, and in PowerShell scripts, will be the name you assigned to it, above.
      3. SQL Clone may report the size of the data image as being substantially larger than the original database, but this is simply due to the dynamic VHD resizing mechanism; the image is just a straight copy of the data and log files.

Summary

Often DBAs can't allow direct access to the production server, for tools such as SQL Clone. However, creating an image from the latest database full backup, or from the full backup plus differential backup, is straightforward.

You can now move on to learn how to:

• Create an image from a SQL Server database
• Start creating clones within the UI – having created an image, many clones, either on the same server or on network servers, can reference this image, independently.
• Automate the provisioning process using PowerShell.