“Rapidly mount live, fully functional database direct from backup”
There are couple of ways to do a restore from a backup file in SQL Server; for instance, using SQL Server Management Studio restore wizard or manually writing your own script in T-SQL. However, these methods are not necessarily the best way to do a restore depending on the scenario you are working with; that is, if you have relatively large database backup file with not much time to do a restore. The above methods occupy large amount of space and take very long time to get a successful restored database.
I have a real work situation where I need to restore a backup database file which has a very large size 167GB and I have to do it in less than an hour. Here where this tool from RedGate (http://www.red-gate.com) comes handy. The product is called SQL Virtual Restore which as the name suggests it virtually restores the database using the backup file itself to mount as live database with fully functional access without the need to do a physical restore. You can try the product for 14days:
The screenshot below shows the actual size of the backup file on the physical drive H:\
To do the restore traditionally, the physical space that is required after the restore is 245GB. Unfortunately, I do not have that much space on my hard drive, so actually the physical restore will fail:
So, the only way forward for me was to use SQL Virtual Restore product by RedGate and it actually worked perfectly because it did not need extra space. It used the backup file itself to mount the live database to. Here is how I did it from the beginning to the end:
You would need the following ready before you begin the installation process:
- Windows Operating System (I am using Window 7 64bit)
- SQL Server (I am using SQL Server 2008 R2)
How to install SQL Virtual Restore 2.3?
1. Download the product from RedGate website:
2. Unzip the compressed file: HyperBacComponents.zip
3. There are three executable files to choose from. For the SQL Virtual Restore, we only need HyperBac_Installer. Choose the correct version for your OS. In my case: HyperBac_Installer_x64.exe
4. Double click on the file HyperBac_Installer_x64.exe and follow the instructions on the screen. i have provided all the screenshots:
5. Activate the product if you have purchased it or click Exit for a 14dyas trial.
6. To open the program, go to Start > All Programs > HyperBac > SQL Virtual Restore Wizard
7. The wizard will start to guide you through 5 steps. Choose the SQL instance where you want the restore appears. I am choosing on my local instance:
8. Click on the Add Files button to browse through Window’s directory in order to select the backup file which you want to have it restored:
9. Keep the default selections or change according to your need. I am specifying a different directory for the database virtual files to be stored in:
10. Summary result is displayed before the final restore. You can also view the T-SQL code by clicking on View Scripts button. Click on Restore button to start the process:
11. You can see now that it has started restoring:
12. In less than an hour the restore completed successfully:
13. You can see from the completed result that we have saved 30% space. Nevertheless, I would say that I saved 245GB of space. Because if I would have done this by a physical restore, I would still have the backup file plus an additional restored database size 245GB; whereas, the virtual restore used 171GB in total including 168GB original backup file plus 3GB additional virtual database files.
Finally, we can see the restored database in SQL Management Studio: