Using Virtual Restore to Attach Databases Directly from Backup Files
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:
Both products come as a 14-day trial, so you can see for yourself whether they work for you or not.
Where to Next?
Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article.
Links in this Document
|Written by||Imar Spaanjaars|
|Date Posted||08/01/2010 15:16|
Talk Back! Comment on Imar.Spaanjaars.Com
I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.
When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.
If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the Doc ID of the document.
Please correct the following errors:
Unfortunately, something went wrong and your message or comments have not been submitted successfully.
There's a fair chance things broke down because you tried to post something that looks like HTML. Things that look like HTML include (X)HTML, obviously, XML, ASP.NET markup and c# generics syntax as all of them use the < and > characters.
If that's the case, try altering your message and remove anything that looks like an angled bracket. You can replace them with [ and ] for example so you can still make it look like HTML to some extend.
If, on the other hand, you were trying to spam this web site, I am pretty glad I caught you in the act and stopped you from doing so ;-)
Also, please don't use links in your posts; I had to block them to filter out most of the junk mail I am receiving.