There has definitely happened something on the file system-level. You notice 2 new files in the folder to which you have restore the backup: One with the extension vmdf and another one with the extension vldf. But why must there be files for a virtual database anyway? Well, SQL Server requires at least 1 database file and 1 log file per database. So from this perspective these 2 files are required. But also for SQL Virtual Restore these files serve a special purpose. Remember, that such a virtually restored database is mounted from a database backup. You certainly would not want someone and/or something to change the content of this backup. SQL Virtual Restore ensures that all data modifications will only ever make it to the virtual database files. The original backup is at any time “safe”. No data modification that was made to the virtual restored database will ever change the content of the original backup. So, if you want you can use your production backup and make this available as a virtually restored database.
After shedding some light on why a virtual database needs some physical representation, let us know have a look at the real surprise:
The original AdventureWorks database consumes roughly 165 MB of disk space. The initial size of the virtual data file is just 128 KB!
Even if you add to this the size of the backup of roughly 34 MB is the initial size of the fully functional database at roughly 1/7 of the original database size. That is quite an impressive saving in disk space that you can achieve and that is the second strong argument for SQL Virtual Restore. However, be aware that even a virtual SQL Server database behaves like a physical one. Data modifications are recorded in its log file and are written to the virtual database file. So, these files will grow in space and should be treated like any other SQL Server database file. This could include taking regular log backups when you operate the database in Full-Recovery Mode and so on…
Let us now come back to SSMS. As you can see the virtual database shows up in Object Explorer just like any other database hosted on that SQL Server instance. From the “look and feel” there is no difference to a physical database.
To demonstrate that there is no difference in accessing a virtual database, let us just give one example. As you can see from the above screenshot, you can query a virtual database just like a “normal” one. In our (by no means scientific) tests we also haven’t noticed a measurable performance hit using a virtual database, if at all. Of course, nothing is for free and there must be some trade-off in handling the virtual database internally, but after all, the intention of SQL Virtual Restore is not to compete in terms of performance with a physical SQL Server database. And for a dev and/or test environment the overall performance is more than adequate.
Conclusion
Red Gate’s SQL Virtual Restore is a pretty cool tool. Nothing more and nothing less. To the best of our knowledge it is the only one of its kind so far. If you find yourself frequently copying backups across the network and restore them on UAT and/or DEV servers in order to provide a production-like environment for developers and/or testers, even if you have this process automated, SQL Virtual Restore is definitely worth having a real close look at. It can cut down the time until such an environment is ready to use dramatically and can save precious network resources, which is a Win-win situation for everyone.
]]>