Christmas Gifts I’d Like to See

Well, we're back at the top of the month again, and this time around for Meme Monday we're to talk about what we'd love to see under the tree from Microsoft. This got my creative juices flowing, and I came up three wishes:

First and foremost, if I had one wish that I could wish this holiday season, it would be that all the children of the world would join hands and sing together in the spirit of harmony and peace.

If I had two wishes that I could wish for this holiday season, it would be for all of the children of the world to join hands and sing, and for SQL Server to natively support object-level restores.

When working with a large database, native backups take a good deal of time to create and restore. For this reason, VLDBs are likely to be backed up using non-native methods such as SAN snapshots, but let's forget about those and stick with what Microsoft includes in the box. If a single table needs to be restored from a backup, the only native way to accomplish this at the moment is to restore the entire database somewhere else and then copy the desired table back to the production server. This takes a great amount of disk space (the entire size of the database) as well as the time necessary to restore all that data. If available, filegroup backups can make it possible to restore only the specific filegroup(s) necessary for said table(s), but even that would be restoring more data than is necessary, since filegroups typically contain more than one table.

The best solution is to be able to restore an individual table or other object directly from a backup. Since only the necessary data would be read out of the backup this would keep the required time and disk space to a minimum. This feature is already offered by several third party vendors, but having it be built-in would be a huge help as you wouldn't have to sit down with your boss and make the case for purchasing yet another tool.

If I had three wishes that I could wish for this holiday season, it would be again for the children, for object-level restores, and for the ability to run integrity checks on a backup.

The time it takes for DBCC CHECKDB to complete increases along with the size of the database being checked, and as databases get larger the general recommendation for integrity checks shifts towards running them on another server instead of the production machine. Once again limiting ourselves to options provided natively by Microsoft, we would have to restore a full backup of our very large database on another machine and run DBCC CHECKDB on it. As before, this will be expensive both in terms of time and disk space.

A few years ago Paul Randal mentioned in one of this blog posts that during his time at Microsoft he developed and patented a way to run DBCC CHECKDB on a backup without restoring it. I can't imagine why Microsoft would sit on this technology, as I know it would be a huge help to me and I'm sure many others could benefit from it as well. Hopefully sometime it can be included as a feature.