SQL Server Virtual Database from Idera

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 SQL virtual database

Fig 1.0

register SQL virtual database

Fig 1.1

download instructions

Fig 1.2

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]

Click the link provided in the email and download the executable

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]

download the IderaSQLvirtualdatabaseInstallationKitBeta

Fig 1.4

Extract the compressed files as shown in Fig 1.5.

Extract the compressed files

Fig 1.5

Installation

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

Double click the SQLvdbBeta-X64.msi file

Fig 1.6

accept terms

Fig 1.7

destination folder

Fig 1.8

user account

Fig 1.9

click install

Fig 1.10

installing Idera SQL virtual database

Fig 1.11

instaqllation of Idera SQL virtual database complete

Fig 1.12

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]

Launch SQLSafe

Fig 1.13

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



Fig 1.14

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

select

Fig 1.15

Click Next. [Refer Fig 1.16]

click next

Fig 1.16

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

Select the compression level

Fig 1.17

Click Next. [Refer Fig 1.18]

click next

Fig 1.18

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

Click on the

Fig 1.19

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

complete the backup

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]

SQLSafe has created the backup file

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]

launch the SQL virtual database tool

Fig 2.2

Click on the Programs-Idera-SQL virtual database-SQL virtual database Console

Fig 2.3

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

click on

Fig 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]

Click on the

Fig 2.5

attach the SQl backup

Fig 2.6

status - online

Fig 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

query the table

Fig 2.8

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]

attach the backup file

Fig 2.9

database attaching

Fig 3.0

database attached

Fig 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]

SQL virtual database tool also comes with a feature that allows you to run queries

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles