How To Extract \ Read Tables from Backup File In SQL Server Without Restoring Database

Today we face a issue where user want some table and procedures from a 2 TB database which we have already deleted and only have old backup. User want to us to restore database on server where over all free space was only 1 TB. 

To save us from this mess one of Application DBA suggested to use SQL virtual database from Idera, Obviously i never heard of this tool so bit of googling and here is download link. Thanks to him for introducing me with this tool. Here i tried to give you quick demo on same.

So What is SQL virtual database: It is a powerful one-of-a-kind solution that lets you attach SQL Server backup files and query them like real databases. With its revolutionary, patent-pending technology, you gain instant access to critical data in a backup file without spending the time and storage previously required for restore. In minutes, you can create a virtual database and then use any native SQL Server or third party tools to query and extract the data you need.

Step1 . Download Idera SQL Virtual Database

Step 2. Install the executable file "IderaSQLvdbInstallationKit-x64.exe" you might get in winzip. Mine is 64 bit you can use as per your server configuration. And Trail period is for just 14 days so do all you Research on this within time

Step3. Installing is pretty simple (next.. next) the only thing you need to be careful is use some account instead local (for me local was not working). I tried my windows account which is admin on machnine and sql server as well.

Step 4. Installation will not take more than a min. At finish window i have select quick launch, you can also start from all programs.

Step 5. Launch window will look like below

Step 6. Now I want to read object from full backup so i will use 1st option on top right "Attach Full Backup"  , If you have have multiple backup files you can use second option as well.

Here you have to fill 3 below things

•  Backup file with location , you can browse also
•  Select server name where you want to see
•  You have to give some name to this db, Give whatever you like even "Ramu kaka" will work :-)

Press "Create"

Step 7: Yes its failed , beacause you didnt give explicit permission on installation folder "Drive:\Program Files\Idera" to your login with below error. Dont forget to make urself owner using advance tab in properties --> security  
  Don't miss permission its very crucial

"An error was encountered while creating Virtual Database 'Test_Partition'. The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'drive:\Program Files\Idera\SQL virtual database\FilterMetaData\server_SQL2014\Test_Partition_data.mdf'. File 'PartitioningDB_Part1' cannot be restored to 'C:\Program Files\Idera\SQL virtual database\FilterMetaData\Server_SQL2014\Test_Partition_data.mdf'. Use WITH MOVE to identify a valid location for the file. The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'drive:\Program Files\Idera\SQL virtual database\FilterMetaData\Server_SQL2014\Test_Partition_data.ndf'. File 'PartitioningDB_Part2' cannot be restored to 'drive:\Program Files\Idera\SQL virtual database\FilterMetaData\Server_SQL2014\Test_Partition_data.ndf'. Use WITH MOVE to identify a valid location for the file. The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'drive:\Program Files\Idera\SQL virtual database\FilterMetaData\Server_SQL2014\Test_Partition_log.ldf'. File 'Partitioning_log' cannot be restored to 'drive:\Program Files\Idera\SQL virtual database\FilterMetaData\Server_SQL2014\Test_Partition_log.ldf'. Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE DATABASE is terminating abnormally."

Step 8. So now i have give permission , Please use retry and now virtual database is online

Step 9. Now how to query 

Connect to ssms open server which you mention during creating virtual db from backup.

Now you can do select , export from table.  But this copy is read only and you can not do any DML operation on database.

Now Once you get your data\ object from this backup. Let remove it 

And before this close ssms and kill all connection to all virtual db other wise you will not able to remove this virtual database.

Now lets uninstall tool. Its always good to finish what you have started. So go to control panel

Now we uninstalled it, We are done. 

Please try this at home but not on your prod servers

Some Imp fact about Idera sql virtual database Tool:

1. Are there disk space recommendations for the SQL virtual database metadata?

Size of Backup	Disk Space Required For Virtual DB
1 TB	105 MB
500 GB	51 MB
100 GB	10 MB
1 GB	105 KB
500 MB	51 KB

2. There is no performance difference between original db or virtual db
3. You can use point in time restore to virtual db also.
4. This will be very useful while recovering database and you are not sure till which t-log you should restore to get specific data.
5. Creating virtual db using Idera sql virtual database doesn't take more than a min.
6. To understand more read pdf : http://www.idera.com/help/sqlvdb/2-1/pdf/sql%20virtual%20database%20help.pdf