Using Virtual Restore to Attach Databases Directly from Backup Files

Quick Overview

As a developer and consultant, I often get backup files from SQL Server databases from clients or developers. I often use these databases to review work done by other developers, or to develop and test against. Quite often, these databases are temporary in nature which means I use them for a couple of days and delete them again. My typical workflow for these backups is like this:

• I receive a zipped archive of the database. As an example, for a current project I am working on, the file is around 500 MB.
• I unzip the .bak file to a temp folder on disk. In my sample project, this ends up as a 1.5 GB .bak file.
• In SQL Server, I create a new database and then use the Backup and Restore wizard to restore the database. This results in an attached .mdf file of another 1.5 GB.

Without cleaning up, the total spaces needed to work with the database is now around 3.5 GB.

With Virtual Restore, I can skip step 3, and instead use the SQL Virtual Restore wizard to create and restore a database directly from the .bak file from step 2. This greatly reduces the disk space needed to store the files and makes it a little easier to manage the files.

However, it can be even better. Part of the underlying technology used by Virtual Restore is HyperBac, a product / technology recently acquired by Red Gate. HyperBac runs as a Windows service and works by intercepting backups created by SQL Server and then compressing and optionally encrypting them. It basically works like this:

• In SQL Server, you create a backup as you normally would, but use an extension recognized (and configurable) by HyperBac.
• Whenever SQL Server creates the backup, HyperBac kicks in and carries out the actions configured for the extension used by the backup file. For example, the .hbc extension is - by default - configured to compress, but not encrypt the backup files.

The cool thing about SQL HyperBac is that it doesn't get in your way. It runs in the background and intercepts SQL Server actions for you without you even noticing. This means you can work with SQL Server the way you normally would, without a change in your typical workflow. Under the hood, HyperBac compresses and / or encrypts your backup files. According to the Red Gate web site, HyperBac can compress backups up to 95 percent. In practice, I am noticing slightly lower compression rates, up to 75 percent. That still is an excellent compression ratio, especially since the files are typically very large, cutting off a large piece from the file. Now, if I can get my clients to make their backups with HyperBac as well, my work flow could look like this:

• I receive a HyperBac zipped archive containing the backup of the database. This compressed file should be similar in size as a normally zipped archive, around 500 MB.
• I use the Virtual Restore wizard and directly hook up the HyperBac archive as a database in SQL Server.

Once I am done with the database, I delete it from SQL Server again. This deletes the .mdf and .ldf files on disk, and releases the lock on the original HyperBac backup file which I can then delete.

You're not locked in to Red Gate's tools because the product ships with a few tools that let you convert a HyperBac backup file to a standard SQL Server backup file. This means that you can convert and use these files normally, without the need to run HyperBac on the server where you're using the backups.

I can wholeheartedly recommend these two tools. For a single developer workstation, the price may be a bit too high, but when used in a production environment with one or more SQL Server installations, these products are well worth their money.

The only thing I wished the product had was a command line or Windows utility to convert to HyperBac files. Converting HyperBac files to normal SQL Server backup files is supported, but I haven't found a way to do the reverse. Being able to do so would mean I could receive a standard and uncompressed backup file, convert it to HyperBac and then attach the compressed backup file as a normal SQL Server database, saving me from a few hundred megs on disk for each attached database. Hint to Red Gate: maybe this is something for the next version? I would like to be able to right-click a .bak file and choose "Convert to HyperBac and Run Virtual Restore" or something like that.

You can find more information about these products on the Red Gate web site:

• SQL Virtual Restore 2.0
• SQL HyperBac

Both products come as a 14-day trial, so you can see for yourself whether they work for you or not.