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
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.
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
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
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.
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.
/****** 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]
create database DB1
create table test (id int, name varchar(100))
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’
/****** 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]
Create procedure usp_querytesttable
select * from test
Now let’s backup the database using the native backup
backup database DB1 to disk = ‘c:\backup\db1.bak’ with init
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
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
Now click on "Attach Full Backup". [Refer Figure
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]
select top 2 * from test
–Script the procedure
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.
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