Everyone knows the difference between a live SQL Server database file and a backup file. However, it seems that this distinction is being eroded, gradually, by the third-party tool providers. First, we saw tools that could compare live databases with backups, in order to report the differences in data or structure. Now we are seeing tools that allow a server to quickly mount, or restore a backup, and read the data from the backup file rather than create an entire database file.

Before such tools appeared, you were faced with having to do a full time-consuming and disk-consuming restore of the appropriate backup files, even if you only needed to perform a simple operation such as to check database consistency, or to query a table.

By contrast, the tools that use a file system filter driver take a database backup file and perform a “virtual restore” operation that mounts a “virtual database” directly to the SQL Server instance. The server has no knowledge of the fact that it is reading from a compressed database backup that is probably about 1/10th of the original size: this is done at file-system level. SQL Virtual Restore, for example, allows ACID-compliant modifications of this virtual database, just like a normal database. Suddenly a whole range of chores become easier, from verifying backups to setting up test cells. You can use it to speed up the offloading reporting from the live database, or refresh development servers with production data.

Why might the hard-pressed DBA pause, rather than immediately jump at an obvious means of saving time and resource? It is, perhaps, because DBAs are valued for their prudence. They are the custodians of the data of their organisations; deeply cautious over matters relating to data preservation and integrity. Whatever time and space-related problems may be attached to the traditional backup and restore, they are well-understood and predictable processes, which is the way DBAs like them. The idea of using a backup file as if it were a live database is a new and alien concept and so one must go beyond expounding the benefits, and explain how and why it works, and what the risks are, if any.

This is why we’ve set Brad McGehee up with a brand new test rig, and a remit to explain and test some of the new generation of database tools to their limits. The first 6500-word instalment is published in this week’s Simple-Talk, covering SQL Virtual Restore.

We’d love to hear what you think. How much testing and research do DBAs need to do with a new tool before accepting that it is fit for purpose in their organisation?

Cheers,

Tony.