Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 21, 2009

SQL Server Virtual Database from Idera

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date