Recently, I wrote a review of Red Gate Software’s new SQL Virtual Restore software. SQL Virtual Restore allows you to quickly turn a compressed backup into a live, virtual database, with the advantage that the virtual database is substantially smaller than the original database, while at the same time acting identically to the production database.

In the article, I described five different ways that SQL Virtual Restore could be used by DBAs to make their jobs a little bit easier. These included:

• Restoring Missing Objects and Data
• Database Corruption Prevention and Backup Verification
• Reporting
• Auditing
• Maintaining Your Development/Test/QA Environments

This past weekend, I attended the Baton Rouge SQL Saturday and I was speaking to fellow SQL Server MVP Kevin Boles. I was talking to a him about SQL Virtual Restore and he mentioned another benefit of the product that I had not thought of, and that is to use it for desktop performance tuning of VLDB (very large databases).

For example, let’s say that your organization has a production database that is 2 TB in size, and that you would like to spend some time hand-tuning some slow-running queries. One option would be to do the performance tuning on the production database, but this is not a great option, considering that you don’t really want to be experimenting on a production server where your “tuning experimentation” could negatively affects the user’s performance. Another option is to backup the database and restore it on another server, where your “tuning experimentation” wouldn’t affect production. Assuming that you have a server with enough disk space, then this would be a viable option. But what if you don’t have a server with enough extra disk space for a VLDB, or what if you would prefer to do your performance tuning on your desktop or laptop computer?

If you use SQL Virtual Restore, you could take a compressed backup of the 2 TB production database and then restore it as a virtual database that would fit onto most desktops or laptops. This is because the virtual database will be much smaller (up to 95% smaller) than the production database. The amount of compression depends on the compressibility of the data in the production database.

Using a virtual database, you could experiment by trying out different versions of the query, knowing that whatever results you discovered from your testing would be applicable to the production database, as the production and virtual databases are identical. And besides just experimenting with different versions of the query, you could also experiment by adding and dropping relevant indexes to see how they would also affect the query.

Once you have completed your query and/or index tuning in the virtual database, you could be confident that when you introduce any changes into your production database, that they would work exactly as they did on the virtual database. This is just one more benefit of using SQL Virtual Restore.