Using Backup Explorer for SQL Server
Backup Explorer for SQL Server is an OLE DB Provider shipped with the HyperBac for SQL Server product which allows administrators to access data from SQL Server backup devices (both native devices and HyperBac compressed and/or encrypted devices) without restoring these devices.
The Backup Explorer OLE DB Provider enables administrators to examine the content of backup devices ore restore data down to row level using SELECT statements connected to Linked Servers, as well as enabling the capability to import data from backup files using Data Transformation Services (DTS), SQL Server Integration Services (SSIS) or development APIs such as ADO and ADO.NET.
Backup Explorer supports backup devices from SQL Server 2000 and 2005. Example usages are described below however as Backup Explorer is a standard OLE DB provider it can be incorporated into any application using the standard OLE DB programming interfaces, making Backup Explorer one of the most flexible and extensible backup object recovery solutions on the market.
Step 1: Create a Linked Server to a SQL Server Backup File Using Backup Explorer
The Backup Explorer OLEDB Provider can be used to view or recover data from
native as well as HyperBac compressed/encrypted backup devices.
In the following example a native backup operation was performed on the
AdventureWorks database as follows:
| TSQL Code: | |
|
BACKUP DATABASE [AdventureWorks] TO DISK = 'C:\Backup\AdventureWorks_Native_Backup.bak' WITH INIT |
Using the Backup Explorer OLE DB Provider installled with the HyperBac for SQL Server installation, you would create a Linked Server as follows, which will allow you to access data in the backup file using Transact-SQL (TSQL) statements:
| TSQL Code: | |
|
EXEC master..sp_addlinkedserver @server = 'ADVENTUREWORKS_NATIVE_BACKUP', @srvproduct = '', @provider = 'HyperBac.oledbmtf', @datasrc = 'C:\Backup\AdventureWorks_Native_Backup.bak' |
A new Linked Server named ADVENTUREWORKS_NATIVE_BACKUP will now
available on the system. This can be accessed through TSQL query batches as
shown in the following sections.
IMPORTANT NOTE: Prior to using the HyperBac Provider for the first time within a SQL Server instance, the OLEDB provider must be enabled for AllowInProcess. This is a once off step per instance, details on how to do this in TSQL are provided below:
SQL Server 2005
As a sysadmin on the SQL Server instance, execute the following command:
| TSQL Code: | |
|
EXEC master..sp_MSset_oledb_prop 'HyperBac.oledbmtf', 'AllowInProcess', 1 |
SQL Server 2000
As a sysadmin on the SQL Server instance, execute the following command:
| TSQL Code: | |
|
EXEC master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Providers\HyperBac.oledbmtf.1', N'AllowInProcess', N'REG_DWORD', 1 |
Step 2: Use the Backup Explorer Provider to Show What Objects are Contained in the Backup File
SQL Server 2005
Using the Linked Server object created in the previous section ADVENTUREWORKS_NATIVE_BACKUP
we can now query the database catalog directly to view the objects in the
backup file (in this case we have specified user table objects in the WHERE
clause):
| TSQL Code: | |
|
SELECT [name] FROM ADVENTUREWORKS_NATIVE_BACKUP...sysschobjs WHERE [type] = 'U'; |
IMPORTANT NOTE: As the Backup Explorer OLEDB
Provider is querying physical data structures with the SQL Server backup device
you cannot query views such as sysobjects. You must query the underlying
physical data table, however this table cannot be directly queried in on online
database.
Other base system tables in the SQL Server 2005 database catalog contained in
the backup file such as syscolpars can be queried directly using the linked
server to get further meta data about objects in the database.
SQL Server 2000
The database catalog differs significantly between SQL Server 2005 and SQL
Server 2000. In this example we will use the Linked Server object created in
the previous section ADVENTUREWORKS_NATIVE_BACKUP we can now query the
SQL Server 2000 database catalog directly to view the objects in the backup
file (in this case we have specified user table objects in the WHERE clause):
| TSQL Code: | |
|
SELECT [name] FROM ADVENTUREWORKS_NATIVE_BACKUP...sysobjects WHERE [xtype] = 'U' |
Other base system tables in the SQL Server 2000 database catalog contained in the backup file such as syscolumns can be queried directly using the linked server to get further meta data about objects in the database.
Using Backup Explorer to Access Data via Linked Server Queries in TSQL
In the following example a native backup operation was performed on the Northwind database as follows:
| TSQL Code: | |
|
BACKUP DATABASE [Northwind] TO DISK = 'C:\Backup\Northwind_Native_Backup.bak' WITH INIT |
Using the Backup Explorer OLE DB Provider installled with the HyperBac for SQL Server installation, you would create a Linked Server as follows, which will allow you to access data in the backup file using Transact-SQL (TSQL) statements:
| TSQL Code: | |
|
EXEC master..sp_addlinkedserver @server = 'NORTHWIND_NATIVE_BACKUP', @srvproduct = '', @provider = 'HyperBac.oledbmtf', @datasrc = 'C:\Backup\Northwind_Native_Backup.bak' |
A new Linked Server named NORTHWIND_NATIVE_BACKUP will now available on the system. This can be accessed in TSQL query batches as follows:
| TSQL Code: | |
|
SELECT * FROM NORTHWIND_NATIVE_BACKUP...Customers WHERE [CompanyName] = 'Ana Trujillo Emparedados y helados' |
This is one example of many you could use to access data which can incorporate any valid Transact-SQL SELECT operation, including JOIN and UNION predicates and more. Furthermore the Linked Server created using the Backup Explorer provider can be used in SELECT INTO batches, enabling the restoration of backup data to a table in an online database as follows.
| TSQL Code: | |
|
SELECT * INTO Northwind..CustomersFromBackup FROM NORTHWIND_NATIVE_BACKUP...Customers WHERE [CompanyName] = 'Ana Trujillo Emparedados y helados' |
IMPORTANT NOTE: The Backup Explorer OLEDB Provider can only query physical base table object and cannot query views or computed columns, in the case that a table includes columns which are computed, specific columns should be referenced by name in the SELECT statement not including the computed column.
Using the Backup Explorer provider with SQL Server Integration Services (SSIS) or Data Transformation Services (DTS)
The example below demonstrates how to interrogate and import data
from a native SQL Server backup device using the Backup Explorer provider via a
SQL Server Integration Services (SSIS) or Data Transformation Services (DTS) operation. The example shown uses SQL Server 2005 Import Wizard (SSIS) however the exact same process flow and logic applies to Data Transformation Services (DTS) in SQL Server 2000.
Selecting 'AllTasks'->'Import Data' from a database in the SQL Server
Management Studio console, from the drop down Data Source menu select Backup
Explorer OLE DB Provider for SQL Server Backup Files.
Select Properties and complete the page as follows, then select Next
from the parent dialog:
Select the import destination from the next dialog, this may be the local or
remote production database that you want to recover backup data to, or a test
or development database, select Next twice. You will now see a list of Table
objects contained in the SQL Server backup device as follows:
Select one or more table objects from the list provided, select Preview to
show the first 100 records in the selected table object. Select Next to
continue with the wizard.
You may now choose to restore the data to a new table or into an existing table
with the same schema definition. The SSIS or DTS job/script can be executed immediately
or deferred and scheduled for later, unattended execution.
It's that easy! Backup Explorer for SQL Server allows you to the fastest, most flexible, extensible means to access data from your SQL Server backup devices, both native or HyperBac. For more information contact us at www.hyperbac.com.