Geeking with Idera's SQL virtual database

A buddy this week (or last I can't remember hell I've been so busy lately) asked me if he could pluck out stored procedures, data, etc. from backups. I told him that SQL Server natively could not but I thought that there were third-party tools to do this. Remember that this is a tricky proposition; "transactional consistency" comes to mind as far as tables go...who knows what was committed and/or flushed to disk at any given time. They had this in SQL Server 6.5 but removed it for SQL 2000 and forward. For the stored procedures, probably a different story. Anyway, I remembered that Idera had a tool that created a 'virtual restore' of a database as they termed it.  So I wanted to check it out for my friend so I went to their site and downloaded the trial.

I have met some of these folks from Idera - they're down from Houston way - when I did my quick stay at Integrated Services Inc/SQL Rx. I thought that they were a fairly intelligent group and had some good products. So I download this thing and then point to a backup and quick as a hiccup here's my database. Nice!  Procs, tables, etc. all there in a database.  Good deal. It works, and we're done!

...Um, I sorta wish I knew what this thing is doing under the covers, though. (This is where the geeking part comes in. If you're not a geek, you probably won't be interested in continuing from this point forward).

1)  I had trouble connecting remotely.  Did I do something wrong?  The tool tried to look at a local instance first.

2)  I point the tool to a backup called AdventureWorksDW2.bak and name my restore AVW2_VIRTUAL1. The database is not online by the way, and only a backup of it exists.  Note that this is my testing database for all kinds of neat stuff as of late and it has files on separate virtual SCSI drives (the whole thing here is a .vhd). It's also a couple of GB in size.  I'll fire up profiler whilst it restores ;)

3) Well crud, it didn't work for this one.  No error. Maybe my backup is no good?  I'll try to restore it and then try for a virtual db. Looks like it's restoring...

4)  Ok it's online let's try again, same .bak file.  Still no workie.  Here's profiler:

So it looks like this thing is doing a create database from a file or set of files, but it's having a problem finding them or generating them.  Just to make sure I'm going to look for any new large files created.  Don't see any. 

I happen to notice that I have 2GB of space left on c:\.   I wonder if this thing is trying to create some files on my o/s partition. 

5)  After shrinking and now I have space, so here goes try #3. 

(a minute later...) Not sure if this was the problem, but it finally came online.  Ok so speed is great, but it creates files for this virtual database?  Hmm. I thought this baby read only from the .bak file?  And, why does it put the files on the o/s partition when all of my database files from the original backup were not on c:\?

Here's the script from profiler to create the databases:

CREATE DATABASE [AVW2_VIRTUAL1] ON
   (FILENAME = 'C:\VDB=SQLVM1!!!AdventureWorksDW2!LSN=30660-2431-1!1.mxf')
   FOR ATTACH

Hmm. Wonder what LSN=30660 and this 2431 is.  Let's look in the backupset table in msdb:

select first_lsn, last_lsn, checkpoint_lsn
from msdb.dbo.backupset
where name='AdventureWorksDW2-Full Database Backup'

first_lsn    last_lsn    checkpoint_lsn
30660000000226400039    30660000000234900001    30660000000226400039
30660000000241300043    30660000000243100001    30660000000241300043

Ok, notice from above that the Create Database script that worked references the first lsn start with 30660 as well as 2431 buried inside of this string.  I have no idea what that is, maybe some other offset?  Looks like this tool is definitely using the log somehow or another, doesn't it?

6) Back to the files, let's run this:

CREATE DATABASE [AVW2_VIRTUAL2] ON
   (FILENAME = 'C:\VDB=SQLVM1!!!AdventureWorksDW2!LSN=30660-2431-1!1.mxf')
   FOR ATTACH

Cool. I get another virtual database copy using the same set of files as from before:

7) If I shut down Idera, I bet it blows away the first virtual db, but not the second (because it never knew anything about this one). Let's see.

Yep, still there. If I close Idera the database is still in SSMS, but I notice that the files are gone now.  Let's query AVW2_VIRTUAL2  ;)

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *  FROM [AVW2_VIRTUAL2].[dbo].[test]

Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: insufficient bytes transferred. It occurred during a read of page (1:232) in database ID 13 at offset 0x000000001d0000 in file 'C:\VDB=SQLVM1!!!AdventureWorksDW2!LSN=30660-2431-1!1.mxf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Uh oh. That's no good. I guess I better delete it...and quit geeking now.

Lee

-------------------------

I successfully proved nothing, and came to no conclusion.  I'm outta here.