SQL Server Virtual Database from Idera

October 21, 2009

Database administrators are often faced with a situation where business users ask them to recover a table from the previous night after an application has deleted an entire table or partial table. Imagine you have a 1TB database backup. It would take the entire day to restore the database and then get to the table.

Idera has created a solution to this nightmare. After 12 years of waiting, finally, a third party tool vendor has put a lot of thought into the problem, stood in the database administrator's shoes and has added a feature to their product that saves a lot of expense, not to mention the DBA's time.

With SQL virtual database, you can attach the backup of your database as your virtual database and query the data without restoring the database backup. Users can also use other third-party tools. For example, users can use Idera’s SQL comparison toolset to compare a live database to the virtual database, script the changes, etc.

This article illustrates how to take a native backup, how to backup a database using either a native backup or SQL Safe, and how to attach those backups as a virtual database.

Register

SQL virtual database is still in Beta' however it works like a champ. Click on the below URL and register as shown below. [Refer Fig 1.0, 1.1 and 1.2]

URL: www.idera.com/SQLvdb

Download and Extract

Once you register, you will get an email instantaneously. Click the link provided in the email and download the executable. [Refer Fig 1.3]

Download Virtual database kit

If you are using 32-bit machine, download the IderaSQLvirtualdatabaseInstallationKitBeta. For this article, I am going to download the IderaSQLvirtualdatabaseInstallationKit-x64Beta.zip. [Refer Fig 1.4]

Extract the compressed files as shown in Fig 1.5.

Installation

Double click the SQLvdbBeta-X64.msi file and follow the instructions illustrated in Fig 1.6, 1.7, 1.8.

Before launching the SQL virtual database tool, let's create a couple of databases and take backups.

Execute the following query on the SQL Server. This query will create a database, DB1, with one table and one stored procedure.

GO
/****** Object:  Database [DB1]    Script Date: 10/13/2009 19:42:25 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]
go
Use master
go
create database DB1
go
use DB1
go
create table test (id int, name varchar(100))
go
insert into test select 1,'Samuel Jackson'
insert into test select 2,'Ivy Kurosawa'
insert into test select 3,'Suki Chiu'
insert into test select 4,'Catherine Jones'
insert into test select 5,'Hong Jiang'
go
USE [DB1]
GO
/****** Object:  StoredProcedure [dbo].[usp_querytesttable]    Script Date: 10/13/2009 19:44:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_querytesttable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_querytesttable]
go
Create procedure usp_querytesttable
as
select * from test
go

Now let's backup the database using the native backup command.

use master
go
backup database DB1 to disk = 'c:\backup\db1.bak' with init
go

Next, let's take a backup of the same database using SQL Safe. Skip this portion if you do not have SQLSafe backup.

Launch SQLSafe, right click on the database and click Backup database. [Refer Fig 1.13]

Make sure the DB1 database is checked and click on the "Next" button. [Refer Fig 1.14]

Now select "Full" backup and click Next. [Refer Fig 1.15]

Click Next. [Refer Fig 1.16]

Select the compression level and click Next. [Refer Fig 1.17]

Click Next. [Refer Fig 1.18]

Click on the "Backup" button. [Refer Fig 1.19]

This will complete the backup as shown. [Refer Fig 2.0]

You can see that SQLSafe has created the backup file, POWERPC_SQL2005_DB1_Full_200910131947 (1 of 1).safe. Note the difference in file size. SQL Safe compressed the backup to 170KB when compared to the native backup of 1361KB. [Refer Fig 2.1]

Launch SQL Virtual database

Now let's launch the SQL virtual database tool. Click on the Programs-Idera-SQL virtual database-SQL virtual database Console. [Refer Fig 2.2,2.3]

Now click on "Attach Full Backup". [Refer Figure 2.4]

Select the C:\Backup\DB1.bak backup file as shown. [Refer Figure 2.5] Click on the "Create" button. This will attach the SQL Backup. [Refer Figure 2.6 and 2.7]

Now let's see if we can query the table and execute the procedure on DB2_VirtualDB. Execute the following TSQL commands on the DB2_VirtualDB database using SQL Server management Studio. You can see that the Virtual database works and responds to the query like any other database on the SQL Server. [Refer Figure 2.8]

use DB2_VirtualDB
go
select top 2 * from test
go
--Script the procedure
sp_helptext usp_querytesttable
go

Now let's attach the backup file, POWERPC_SQL2005_DB1_Full_200910131947 (1 of 1).safe, using the SQl virtual database tool. [Refer Figure 2.9,3.0,3.1]

SQL virtual database tool also comes with a feature that allows you to run queries. Click on the "Run Queries" button from the tool. [Refer Fig 3.2]

Note: This product supports SQL Server 2000 SP4 and SQL Server 2005. IDERA plans to release a version with SQL Server 2008 support by the end of this year.

Conclusion

This article illustrated how to take a native backup, how to backup a database using SQLSafe and how to attach those backups as a virtual database.

» See All Articles by Columnist MAK