Recently I saw a post by SQL Server MVP Greg Linwood entitled "Saving the Environment" and spent some time following and participating in the discussion. This was on a SQL Server related forum, so it was an intriguing post to say the least.
Greg's idea was to get rid of indexes in backups. After all, indexes could be 20%, 40%, or even 80% of your data size. In some cases, the non-clustered indexes might even be larger than the clustered index! If you could eliminate this from your backup, then you'd save a lot of resources: time, power, media, and more.
Thus helping lower the impact on the environment as well as your bottom line.
This is a very interesting idea, and could be a huge performance boost to not only your backup procedures, but also your DR plan.
Now these indexes must be rebuilt if you restore the database, and that takes resources, but if your backup were 1/2 or 1/4 of its current size, the restore would happen quicker. Indexes can be rebuilt online, so your database could be available quicker, and it's possible that separate threads could even be spun off inside the restore process to start rebuilding indexes as the restore completes.
And potentially you could even have a switch to "not" rebuild indexes, which would be a great boon for those test restores (you do test restores, right?) or restores for reporting or other purposes where you might create a different indexing strategy.
If you agree, and I do, take a minute and add a vote on Connect. Greg's proposal is here: Backup / Restore without index data
And if you have any ideas of your own, please drop them on Connect, send them to me, or better yet, write a guest editorial for me and send it in 🙂
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 30.1MB WMV
- iPod Video Podcast - 23.7MB MP4
- MP3 Audio Podcast - 4.9MB
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.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.