26 April 2017
26 April 2017

Using striped backups with SQL Clone

Can we use a striped backup as the source for a SQL Clone image? Yes we can!

If you’re a Redgate SQL Backup customer, occasionally you’ll need to convert your SQL Backup (.sqb) files to the native SQL Server backup format (.bak), perhaps to perform native database restores on a server where SQL Backup isn’t installed. This produces a striped backup, because each thread used when making the backup will produce a separate file.

Can we use a striped backup produced in this way, or indeed any striped backup, as the source for a SQL Clone image? Short answer: we can! Let’s see how that works.

Create the striped backup

I used the SQL Backup file converter (sqbconverter) to convert an .sqb backup file for my PartsUnlimited database to a native, striped backup. By default, this stripes the backup across seven files, as shown in Figure 1:

striped backups
Figure 1

To restore this database in SQL Server, I need to include all seven files in the RESTORE DATABASE command. Likewise, if I want to use SQL Clone to create an image from this striped backup, I’ll need to include all seven files.

Creating an image from a striped backup

To create a new image from a striped backup using the SQL Clone GUI, choose Backup as the source type for the new image, and click Next to move to the Backup location dialog, where you need to enter the location of the backup files, and choose a SQL Server instance for the “pass-through” restore. SQL Clone restores the backup directly into the image, so no temporary storage is required, and the imaging process takes about the same time as a normal database restore operation.

The backup location defaults to the instance backup location, which is the default SQL Server backup location. I have one only instance registered on this machine, so I only see the one choice:

Figure 2

I still need to specify the backup file name(s), on the screen shown in Figure 2. The important part for striped backups is to specify one file path per line, as indicated in the help text below the box. In other words, we’ll have seven lines, each one indicating the path and file name for each file.

I learned a good trick from Gianluca Sartori (@spaghettidba) in a tweet, which makes this task easy. Highlight all seven files, hold down the Shift key while right-clicking, and then select Copy as path from the pop up menu:

Figure 3

Finally, simply paste the contents of the clipboard into the backup file name(s) box. The path for each file will be enclosed with quotes, and you need to remove those, as shown in Figure 4:

Figure 4

If you forget to remove the quotes, SQL Clone won’t let you move forward and you’ll see an error message:

Figure 5

Once the paths are correct, the rest of the process is easy and you just need to choose an image destination. I’m using a local folder on my test machine, but development teams will want a network path (or a share).

Figure 6

Now give the image a name. I don’t need to denote the source as a striped backup, but I did for this demo.

Figure 7

That’s it. Click Create Image, and SQL Clone will produce the image. The time required to build the image depends on your backup size, but this is a one-time cost. After that, each database clone produced from that image will be created in seconds, and occupy only a few tens of MB on the target machine.

SQL Clone works with any striped backup

SQL Clone works with any type of SQL Server database backup, including native backup files (.bak), striped or otherwise, SQL Backup files (.sqb), and .sqb files that have been converted to native striped backups.

Having created the image, creating clones for your developers, your testers, for team-based database development, parallel test runs, or for any other purpose, is quick and easy.

All of which is reason enough to take advantage of SQL Clone’s 14-day, fully-functional free trial and discover what it can do for you.

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

    Minimizing the impact of data breaches in dev and test databases

    Data privacy regulations make it essential to implement controls and processes that protect personal data and guard against data breaches. Join Microsoft MVP Steve Jones for a webinar on how to minimize the impact of data breaches in SQL Server dev and test databases.

  • Webinar

    Data privacy & protection: A logical extension to DevOps

    Are you considering data privacy and protection as part of your DevOps process? In light of legislation like GDPR, making sure that any personally identifiable information (PII) is protected as it moves through your development and testing environments, is now an essential part of the process to ensure that your Database DevOps practices are compliant.

  • Article

    Creating Multiple Masked Databases with SQL Provision

    Chris Unwin explains how SQL Provision can create copies of multiple databases, each masked consistently, and deliver them as a group. This is useful when, for example, you are working with a Data Warehouse that contains several cross-database relationships.

  • Article

    Deploying and Reverting Clones for Database Development and Testing

    It can be quite a shock for developers to realize they can make radical changes to the data or schema, while testing, safe in the knowledge that it will take them only a few seconds to revert the database to its original state. Phil Factor demonstrates how it's done, using SQL Clone and PowerShell. It means you easily run a series of rapid-fire database tests (run a test, reset the clone back to how it was, run another test, and so on).

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant