Over the last months, I have been putting a considerable level of effort recovering databases.  That would be databases in dev, staging and prod.  Somewhere, somehow someone got access to Prod and decided to mess with it.  All in all this has created some heartburn and so on.  Again, not only from the downtime it has caused and the lengthy recovery process.

We figured we could use interim solutions that would become active only when we knew a transition period had arrived, while effective, the changes started to become larger and larger and therefore the interim solution started losing effectiveness.  All while backups were/are running (I don’t see backups as optional, in any of the environments).  The data changes and test situations are critical in each environment that backups are needed to safeguard data in structure, form and functionality.

So, as I mentioned we had been running in a combination Backup and DB Snapshot scenario.  The backups are becoming larger and larger, to the point of working with a transfer of a 45 to 70+ GB backup gets ridiculous at times.

Enter SQL Virtual Restore from Red Gate software. With this tool I am able to reuse the backups that are existing in the environment and go through the process of a Virtual Restore in the event of needing recovery.  So, what makes it cool, interesting, life saving?  The fact that it uses the backup file, compressed and all to generate the virtual components.  It cut down the time to recover by at least 30%, but even better? 88% space saved!  Again, the goal, being able to perform recovery.  This means too, being able to do Object Level Recovery.

It is early in the process of adoption of the tool, but the results are proving to save us time in recovery, space and network transfer rates.  Huge benefit!

Follow along on the sample process I conducted:

1) Opened to the tool and pointed to the SQL Instance to recover to.

4-23-2011 6-53-19 PM

2) Selected the backup file location and file to restore

4-23-2011 6-53-36 PM

4-23-2011 6-54-06 PM

4-23-2011 6-54-17 PM

3)Specified the destination

4-23-2011 6-54-42 PM

4) Reviewed Summary

4-23-2011 6-54-56 PM

4-23-2011 6-55-14 PM

6) Process Completes and gives you a summary of execution and storage saved (This is not my real application, but rather an AdventureWorksLT test)

4-23-2011 6-55-30 PM

4-23-2011 6-55-43 PM

4-23-2011 6-55-54 PM

5) Validation through SQL Server Management Studio

4-23-2011 6-57-20 PM