Simple Virtual Database Restore Example

SQL Virtual Restore by HyperBac provides a simple and effective way to view, compare and selectively recover objects and data from SQL Server backup files without performing a full database restore operation, saving both disk space and time.

SQL Virtual Restore is fully flexible and programmable, allowing administrators to perform virtual database restore operations from TSQL, PowerShell, SMO and other interfaces. Additionally, SQL Virtual Restore is fully extensible allowing virtualized restore operations including Full Backups (including multiple file or "striped" backup devices), Differential Backups and Transaction Log Backups.

A straightforward example using the SQL Virtual Restore wizard user interface involving a single full backup file is provided below.




Step 1 - Connect to an instance of SQL Server




Step 2 - Select Backup File(s) to Virtually Restore
Full, differential and/or transaction log backup devices can be provided at this time, for the purposes of this simple example a single full is provided.




Step 3 - Specify Virtual Restore Options
These options include:

• Database Name - The name of the virtualized database to be created


• Virtual Database Files - Locations for the virtual data and log files to be used during the Virtual Restore operation, these are typically only 16MB in size


• Recovery Completion State - The state of the database after recovery, this can be any legal SQL Server database recovery state, including an online state(WITH RECOVERY) or a read only state (WITH STANDBY)

Step 4 - View Summary




View Virtual Restore Operation Progress




Virtual Restore Operation is Complete




Virtualized Database in SQL Management Studio
After the virtual database restore operation is complete you now have a fully functional SQL Server Database running from the backup file or files used in the Virtual Restore operation, without requiring the additional space normally required to perform a restore operation.

Examples of more complex operations will be provided in future articles. For more information or to download a fully functional evaluation copy of HyperBac SQL Virtual Restore 1.0, visit the HyperBac website.

Introducing SQL Virtual Restore

We’re pleased to announce the general availability of SQL Virtual Restore 1.0.

SQL Virtual Restore enables database, systems, and SharePoint administrators to run fully accessible and functional SQL Server databases directly from backup files without having to perform a physical restore operation. This offers significant space savings, as the SQL Server database mounted from your selected backup files by SQL Virtual Restore carries a very low storage footprint. This also means that the data in your backup is available to you faster than if you had to wait for a restore operation to be written out to disk.

SQL Virtual Restore can be used for object level recovery, database auditing or to recover SharePoint documents from SQL Server backup files without requiring a SharePoint Site database restore operation. SQL Virtual Restore can also be used to create development, test or read-only databases from backup files, using minimum disk space on the target system.

SQL Virtual Restore is built upon unique HyperBac Online data compression technology and supports native SQL Server backup files as well as HyperBac compressed and/or encrypted backup files. We will soon offer support for Red Gate SQL Backup files too – more information is on its way!

. SQL Virtual Restore provides full support for all data types and structures, including FILESTREAM data. It also fully supports the SQL Server database upgrade process (allowing users to create a virtualized database on SQL Server 2008 from a backup file created on a previous version of SQL Server, e.g. SQL Server 2005).

In addition, complex virtual restore operations are fully supported, including those using differential backups, filegroup backups, transaction log backups, and more. This enables administrators to virtually restore and recover a database to provide a discrete snapshot or reference of what a SQL Server database looked like at any particular point in time.

SQL Virtual Restore is the result of a collaborative effort between the Red Gate and HyperBac development teams. (Check out the SQL Virtual Restore wizard developed by the Red Gate team that moves you through the process of mounting a SQL Server database from your backup file in four simple steps.) For more information or to access a fully functional evaluation version, visit the HyperBac website.

We’re also really keen to hear your feedback on new SQL Virtual Restore. Please do get in touch with us to let us know how you’re using SQL Virtual Restore, the performance you’re seeing, and if there’s anything you would like us to consider for future development work.

HyperBac Acquired by Red Gate

Excited to announce the acquisition of HyperBac Technologies by Red Gate Software. We have been working with Red Gate for some time and we now have officially joined forces.


All commitments for existign customers relating to technical support and pricing will be fully honored.


The HyperBac technolgy and products will continue to evolve under Red Gate and we have many more exciting product's to release in the coming months.


For more information about the acquisition see the Press Release and Acquisition FAQs.

SSAS Backup Compression Using HyperBac

HyperBac allows you to apply streaming compression and/or encryption to Microsoft SQL Server Analysis Services backups. In many cases the compression obtained in greater than native SSAS backup compression in SQL Server. As seen below an SSAS backup performed with HyperBac is half the size of a comparable backup performed using native compression in SQL Server 2008 Enterprise Edition:


Compressed SSAS backups can be obtained with any version of SQL Server Analysis Services (including 2005 and 2008), and can be performed using any version of HyperBac. In version 4.2 of HyperBac, the functionality is embedded in the HyperBac license however it is capable with previous versions of HyperBac by consulting with HyperBac sales or technical support staff.


Follow the steps outlined below to obtain streaming compression of a SSAS backup operation:


Configure the SSAS Backup Directory for HyperBac


In the Extensions tab of the HyperBac Configuration Manager, click Add


Add either the .ABF file extension or the SSAS Backup Directory (as we have done in the example below going through the relevant dialogs in the Add Extension wizard:


Register the SSAS backup directory and all sub directories


Register all file extensions for this directory (alternatively you can specify only the .ABF file extension


Specify HyperBac compression


Optionally Specify HyperBac encryption (not done in this example)

Specify Use Oracle format (required for SSAS backups)


Affinity tab skipped in this example


Click on OK and exit the HyperBac Configuration Manager


Perform your SSAS backups as you would normally (via script or SSMS)


Use the backup directory specified in the previous step.





The Results


IMPORTANT NOTE: You must be either using HyperBac version 4.2 or have requested a special license from HyperBac sales or technical support staff enabling SSAS compatibility to enable this function.

For more information see SQL Server Analysis Services Backup Compression

HyperBac Wins Best Backup Award

HyperBac for SQL Server has won the Gold award for Best Backup and Recovery Software Product for SQL Server in the Windows IT Pro/SQL Server Magazine 2009 Community Choice Awards.

This is a tremendous accolade, especially as it was voted on by the SQL Server community and by SQL Server DBAs in particular.

SQL DBAs have spoken, and have chosen HyperBac and it's unique approach to storage compression, data security and object level recovery and it's extensible architecture as the most flexible, most seamlessly integrated and best value for money solution available on the market.

For more information see HyperBac Wins Gold Award for Best Backup and Recovery Software Product for SQL Server

LogShipping Made Easy Using HyperBac

This article shows you how to use HyperBac to compress and encrypt your backups when deploying SQL Server Log Shipping. Just follow the steps outlined below and you will have the additional benefits of encryption and compression for your log-shipped database backups. Configure SQL Server Log Shipping using Mgt Studio and then install HyperBac on both the Primary and the Secondary Log Shipping Servers. Note that even though there are two servers in the mix, you only need to purchase one license for your primary server. This is because HyperBac allows you to restore/test your backups on multiple servers using a free restore-only licenses for a small maintenance fee. So you can just use one of these licenses on the secondary server.

The list of steps to configure Log Shipping are:

Step 1) Fire up HyperBac Configuration Manager and go to the “Extensions” Tab.
Step 2) Then click on "Add" Button and browse to the location of your LogShipping Backups.
Step 3) Click on the "File Extension" Tab and choose "Include All Extensions".
Step 4) Click on "Compression" Tab and choose "Enable HyperBac Integrated Compression".
Step 5) Click on "Encryption" tab to enable encryption and the relevant encryption level.
Step 6) Click on the "Format" tab to choose Backup Format only.
Step 7) Click on "Affinity" tab if you decide to limit the backups to a particular processors .

You can repeat the steps outlined above for your full database backup locations as well as for your tlog backup locations (if they exist in different locations). You will thus end up with two new extension settings in the HyperBac configuration manager.

I have posted the relevant screen shots for the steps below:

Step 1) Fire up HyperBac Configuration Manager and go to the “Extensions” Tab


Step 2) Then click on "Add" Button and browse to the location of your LogShipping Backups. Note that if the location is specified using UNC in the Mgt Studio log shipping configuration then you will have to specify the same here (for eg. the same location can also be specified as \\hbtech1\TPCH_LogShippingBackupsDestination)


Step 3) Click on the "File Extension" Tab and choose "Include All Extensions"


Step 4) Click on "Compression" Tab and choose "Enable HyperBac Integrated Compression"


Step 5) Click on "Encryption" tab to enable encryption and the relevant encryption level.


Step 6) Click on the "Format" tab to choose Backup Format only.


Step 7) Click on "Affinity" tab if you decide to limit the backups to a particular processors .



After progressing through the above steps when you click on "OK" then your custom extension settings will display in the configuration manager as shown below:


[Note: Due to a known MSFT limitation compressed backups cannot exist on the same device as uncompressed backups. Hence hyperbac will not try to compress your backups if you append your logShipping backups to an existing uncompressed backup device. If you want to use compression in this case, you will need to reinit the backup using the 'with init' option]

Run Oracle Databases Compressed with HyperBac

HyperBac for Oracle versions 4.0 and above introduce online compression capabilities to complement existing RMAN backup, export (EXP) and export data pump (EXPDP) compression and encryption capabilities.
The steps below describe a simple scenario to backup a tablespace to a compressed file and then restore this compressed tablespace backup to a compressed datafile and run the Oracle tablespace on this datafile, reducing space and improving read IO performance.

Step 1: Perform a compressed RMAN tablespace backup of the EXAMPLE tablespace using HyperBac

C:\Users\Administrator>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Aug 30 21:03:22 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1223623114)

RMAN> backup tablespace EXAMPLE format 'C:\Backup\Oracle\EXAMPLE.HBC';

As seen from the following screenshots the tablespace backup file is compressed by over 70%.


File size comparison


Tablespace Backup File Meta Properties shown in HyperBac WinExtractor

Validate the HyperBac compressed tablespace backup using RMAN

C:\Users\Administrator>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Aug 30 21:03:22 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1223623114)

RMAN> restore tablespace EXAMPLE validate

Starting restore at 30-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=130 devtype=DISK

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece C:\BACKUP\ORACLE\EXAMPLE.HBC
piece handle=C:\BACKUP\ORACLE\EXAMPLE.HBC tag=TAG20090830T210524
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
Finished restore at 30-AUG-09

Steps 2 and 3 are performed in SQLPLUS connected as SYSDBA

Step 2: Take the EXAMPLE tablespace offline

ALTER TABLESPACE EXAMPLE OFFLINE TEMPORARY;

Step 3: Change the file names for the active datafile(s)

SET NEWNAME FOR DATAFILE 'C:\oradata\EXAMPLE01.DBF' TO 'C:\oradata\EXAMPLE01.DBFX';

Step 4: Restore the HyperBac compressed tablespace backup to a compressed datafile

RMAN> RESTORE TABLESPACE EXAMPLE;

Step 5: Recover the tablespace

RMAN> RECOVER TABLESPACE EXAMPLE;

Step 6: Bring the tablespace online

SQL> ALTER TABLESPACE EXAMPLE ONLINE;

The comparative before and after results of datafile compression with HyperBac are shown below:


EXAMPLE Datafile Without Compression


EXAMPLE Datafile With HyperBac Compression

You can now perform routine database functions as normal including INSERT, UPDATE, DELETE and SELECT operations (as seen below). The underlying datafile remains in a compressed (and/or encrypted) state in this case saving over 70% in raw storage space.



Furthermore backups of this particular tablespace will be further accelerated as both reads and writes are compressed. This yields the absolute fastest backup possible using any method.

For more information see Oracle tablespace compression using HyperBac