I have restored a 50GB DB on a 20GB-disk

NEVER COMPRESS A DATABASE-FILE!

 

Well, I didn’t! I just virtually restored the database.

 

Eh? What?

 

Time for some explanation:

I have a customer that has a database of about 50 GB of data. And all the files are about 60GB.

The native compressed SQL backup (they use SQL 2008 Enterprise) is about 8GB. No data or page compression is used.

 

As we all know, there are some problems to restore a big database.

You need a lot of disk space because SQL has to recreate the files as they were when the backup was made.

Recreating those files also requires a lot of time (especially in a virtual machine in which the disks are not pre-grown to the maximum size and the virtual machine resides on a portable).

 

So, how can we avoid these 2 problems. Well with Red Gates’ SQL Virtual restore (http://www.red-gate.com/products/SQL_Virtual_Restore/index.htm).

 

What does it do?

In short: when you restore a backup, it makes SQL think it is really restoring it, but without SQL knowing it, it is creating it’s own files with some limited information in it. The data itself remains in the backup-file.

So instead of creating (BIG) lfd,mdf,ndf-files, it creates small vldf,vmdf,vndf-files.

And it does that quite fast.

What are the final space requirements for restoring this 8GB backup file? Well the 8GB backup file + a handful of MB for the vlfd,vmdf,vndf-files!

And your database is fully operational. You can read from it, you can write (!) to it, you can make another backup of it. You can use (and abuse) it how you want.

And NAV? Is NAV working well? Consider this : SQL server does not even know it has been tricked by Red Gate’s SQL Virtual Restore. For SQL it behaves like any other database. So NAV has no problem at all working with it.

And how about performance? Well, reading-performance is close (or even better) to a real database because it does a lot less reads on the disks. It does use more the processor to decompress the data it read from disk (the data is NOT compressed in memory [at least if you are not using data or page compression in the original database]).

So if your virtual machine has only 1 processor, you might feel it (NEVER give 2 cores to your virtual machine if you only have 2 cores on your portable. It is generally slower.)

Writing-performance, on the other hand, is quite bad because the writing is not done in the real files, but in the v???-files (the backup-file is NEVER changed!) . This is definitely a negative, but depending on what you want to do, this negative point can be a small one.

 

How can this be useful for NAV?

-How many times it happens that the customer has some problem and you need a backup so you can test it? And then you need to restore it on your server. If your customer has a BIG database, this is not a minor problem. Now you can just take the compressed backup (and if your customer does not have SQL server 2008 Enterprise or 2008 R2 standard, you can let your customer buy Red Gate’s SQL backup or Hyper Backup) and virtually restore it on your server. It is a lot faster and takes a lot less space. And even if you don’t have a compressed backup, it will be still a lot smaller than a full-fledged database (the backup file does not contain unused space in the database or transactionlog files). And the write performance is not a problem, because you just need to find the problem, so writing is very limited.

-Also the customer can use it. Instead of making a full backup+restore on another server for reporting, and needing a big disk (and a lot of time to restore) on the reporting server, it is enough to virtually restore your backup. Writing speed is completely unimportant here.

 

Is it reliable?

I think it is. I have a small story that I think proves the quality of the product:

The first tests I did with the 8GB backup, created a file of about 4GB. Having read the information, I thought that this was too much (well, 12 GB is still only 20% of the original). I know there was a webcast so I waited until the webcast to ask some info about it. I was directly contacted by the product team to help me find the problem (first wow! After all, I am not a SQL Server MVP or know in the SQL server community). It took some test-versions before they found the problem. In the end it turned out to be a setup-problem.

But what really surprised me about the product is that I could remove the old version, manually delete some left-over files and install the new version. And I did this several times and I NEVER NEEDED TO RESTART WINDOWS! (second and BIG) WOW!!!!!

 

Some more info:

http://www.bradmcgehee.com/2010/08/how-to-performance-tune-vldb/

http://www.simple-talk.com/sql/sql-tools/brads-sure-guide-to-sql-virtual-restore-/

Leave a Reply

You must be logged in to post a comment.

Create a new blog and join in the fun!