Editor's Note: Steve Jones is an employee of Red Gate software.

.

I think SQL Server is an amazing product, and I'm always thrilled to see new features that expand the list of things you can do with the platform. It's not that often that I find the platform lacking, and can usually make it work for me. However there are a few cumbersome processes that have shown the rough edges of SQL Server over the years.

I started really managing SQL Server with v6.x. I had used SQL Server v4.2, but was more of an administrator keeping the server going for third party products. With v6.x at a few companies I deployed, upgraded, and recovered servers on a regular basis. One of the truly rough edges that existed in that version was the need to actually know the sizes of each allocation of disk space for your database. We didn't have auto-grow back then; you had to manually add space to the database AND keep track of that allocation size for DR purposes. A royal PIA that had me cursing the developers at Microsoft more than once.

We don't have to worry about that anymore, but one thing that still bothers me, and regularly causes issues for other people working with SQL Server is the need to restore a database with the same size files as it had when the backup was performed. That kind of makes sense; if I had 1 TB of data when I made the backup, I need 1TB of space to do the restore.

However in production many of us have free space in our system to handle data growth, routine maintenance and more. A database with 1TB of data might be sized at 2TB on the original server. My dev server, with 1.5TB of space, can't restore the backup, even though there is only 1TB of data to restore. This is one thing I've wished Microsoft would fix, and I'm still wishing.

Idera has had SQL Virtual Database to help here, but now there is a new way to handle this. Red Gate Software, who purchased Hyperbac earlier this year, is releasing their virtual restore capability with SQL Virtual Restore. This allows you to "mount" a backup as a live database, with the ability to read from and write to this database, just as if it were a regular database. To SQL Server it seems as if a restore has taken place, and it is using files on your server that are the same size as the original mdf/ldf/ndf files. The cool thing is those files are sparse files, and don't take up the space a physical restore would require.  If you can access the backup from your system, you can "restore" it as a database you can use: to read from and write back to¹.

Editor Update: I have implied that Idera's Virtual Database is read only here since that was my impression from talking with other people and from information on the Idera site. That is not the case. Idera has similar read/write capabilities, just as Virtual Restore.

There are lots of possibilities here for development, troubleshooting, object level recovery and backup verification with this new product. I think SQL Virtual Restore is a very cool new tool that might help you deal with the ever growing space issues that come with large databases.

Steve Jones

1: Note that this technology is interesting in how it works. Your backup is not changed. You read from the compressed backup, but write back to the sparse MDF/LDF files. You can even take a new backup of this database with your changes inside.

The Voice of the DBA Podcasts

• Windows Media Podcast - 26.4MB WMV
• iPod Video Podcast - 21.2MB MP4
• MP3 Audio Podcast - 4.9MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter: