In the 15 years I have been a DBA, I have seen a lot of new SQL Server tools. Most of these tools fit into discrete categories, such as database modeling tools, schema compare tools, or backup compression tools: but every once in a while you run into a product that defies simple categorization, and one such product is SQL Virtual Restore from Red Gate Software. In brief, what SQL Virtual Restore does is to mount a SQL Server backup to a SQL Server instance as a fully functional, live database. In other words, the mounted database works exactly like any other database from SQL Server’s perspective, except the data, for the most part, is stored in a backup file.
“So what?” you might ask. “Can’t you just restore a database backup to accomplish the same goal?”.
You can, but SQL Virtual Restore offers several important benefits to DBAs that a standard restore can’t provide: substantial space savings and substantial “restore” time savings. Here’s what I mean.
Substantial Space Savings: When you perform a regular physical restore of a native backup file to SQL Server, the resulting database’s data (MDF) and log (LDF) files are stored on disk in an uncompressed format. In addition, most database files have a lot of “empty” space that is not currently being used. For example, you might have a database with a 100 GB MDF file, of which only 60 GB is used and of which 40GB has been pre-allocated for future growth. In fact, it is very common, and considered to be a best practice, to pre-size MDF files to a future expected growth size so that you don’t have to rely on autogrowth to grow your databases. The same applies to LDF files.
On the other hand, if you use SQL Virtual Restore to mount a compressed database backup as a fully functional database, instead of restoring the normal 100 GB database, there can be a large savings in space. For example, not only is the unused space in the database (40 GB in our example) not stored as part of the backup, the remaining data (60 GB in our example) is compressed; sometimes resulting in up to a 95% compression ratio (the amount of backup compression depends on the backup compression technology and the type of data stored in the database). To be conservative, let’s assume that a 100 GB database can be compressed by 80% using backup compression, which would result in a 12 GB compressed backup file (100 GB – 40 GB = 60 GB, 60 GB x .2 = 12 GB). If you mount this compressed backup as a database instead of the full, 100 GB database, you would be saving 88 GB of space, a huge space savings. Keep in mind that the mounted 12 GB database, from the perspective of SQL Server, acts exactly like the 100 GB database, except it is 88% smaller.
Substantial “Restore” Time Savings: One aspect of performing a standard restore of a database backup that is often overlooked is the amount of time it can take for the restore to complete. For very large databases, this can be very time consuming.
SQL Virtual Restore, on the other hand, doesn’t perform a “standard restore”. Instead, it mounts the backup directly to SQL Server. In my testing, mounting a database can be up to 75% faster than performing a standard database restore. The reason a virtual restore is faster than a standard restore is because less data has to be restored (e.g. a compressed backup vs. an uncompressed backup), reducing the I/O (and time) required to mount the database.
The time that can be saved by mounting a compressed backup instead of restoring a backup conventionally will vary depending on the type of compression used for the backup. In our example, let’s assume that it takes 25 minutes to perform a standard database restore of a 100 GB database. To be conservative, let’s assume, in our example, that the mounting time for a compressed backup is 50% faster, which means that mounting the compressed backup, as compared to performing a standard restore, is now 12.5 minutes instead of 25 minutes. While the time savings may not seem like a lot on a smaller database, it can add up if the databases are multi-terabyte in size.
Note: Although the product is called SQL Virtual Restore, it has nothing to do with server virtualization, such as Hyper-V or VMware, nor with the virtual server names used in clustering. In the context of SQL Virtual Restore, when a compressed backup is mounted to a SQL Server instance, it is called a virtual database, while still retaining all the functionality of a normal database.
In this review I will put SQL Virtual Restore to the test and explore its various uses and benefits. In addition, I will show you how it works, how to install it, and provide an example of how it works.
What are Some of the Benefits of Using SQL Virtual Restore?
OK, now that you understand how using SQL Virtual Restore can save substantial amount of disk space, along with saving restore time, why would a DBA even care about this? In fact, there are many reasons why this is beneficial. Let’s take a look at some of the most important ones.
Restoring Missing Objects and Dat
How many times, as a DBA, have you been asked to restore a table, a view, a stored procedure—just to name a few of the many different database objects—from a backup at a specific point in time because it was accidently modified or deleted and can’t be recovered any other way? If this is the case, one option is to restore the entire backup in order to retrieve the missing object or data. If the database in question is large, performing this task could be time consuming (if a long restore is required), or perhaps not even possible if you don’t have enough extra disk space to restore the database. While disk space may be inexpensive, not all DBA shops have lots of extra space available, especially if the database that needs to be restored is very large.
Another option to retrieve missing objects and data is to use a third-party object restore tool that allows you to extract the object you need restored from an existing backup. Depending on the technology used, some of these tools only allow you to retrieve tables, and not other objects. In other cases, retrieving a single table is problematic because of referential integrity issues. For example, if the requested table to be restored is a sales header table, but if it has a foreign-key relationship with a sales detail table and a vendor table, then all three of these objects need to be restored. And this doesn’t even consider the issue with changes in the data that occurred since the backup occurred.
SQL Virtual Restore, on the other hand, removes the obstacles described above. You can quickly virtually restore an entire database from a backup with minimal disk space. And since you now have all the objects (and their data) at hand, you can then retrieve and restore the missing object or data as appropriate. You can also do this knowing that the data in the restored database is fully consistent, with its Declarative Referential Integrity (DRI) intact.
Database Corruption Prevention and Backup Verification
In a perfect world with unlimited resources, it would be a great idea if every database was checked on a daily basis for potential database corruption using the DBCC CHECKDB command. This is because the earlier you find any database corruption, the sooner you can fix it, reducing potential data loss. Unfortunately, running DBCC CHECKDB is a time and resource intensive process. Because of this, many large databases are only checked weekly or monthly, if at all.
Along the same lines of thinking, ideally every database backup should be verified that it is good after it is made. This is because backups can become corrupt themselves, rendering this unusable, and the only way to know for sure if a backup is good is to restore it. But like running DBCC CHECKDB, this can be a time consuming process, plus it takes disk space to restore the backups in order to test them. This can be a problem if your database is multiple terabytes in size.
SQL Virtual Restore can help solve this problem. For example, you could take a compressed backup of the database nightly, then virtually restore the backup to another SQL Server using SQL Virtual Restore, then run DBCC CHECKDB against the backup. This kills two birds with one stone. Not only are you testing the integrity of the backup, you are also testing the integrity of the database. In addition, the DBCC CHECKDB is completed on another server, so it won’t affect the performance of the production database. Because a SQL Virtual Restore database uses so much less space than the original database, and virtual restores are much faster, it makes the above process feasible. Something that is otherwise not generally practical for very large databases.
Full article...
