I've always told DBAs and developers working for me that backups don't really matter. After all, 99.99% of the time we make a backup of a database, we never go back to it. It sits there, on some media, until it's deleted, erased, destroyed, or perhaps, it just lives on.

Restores, however, are critical.

No one ever got fired for not doing a backup that wasn't needed. However not being able to complete a restore has damaged more than a few careers. Just like being able to do that restore can boost your status as a DBA.

Last year, Greg Linwood posted a blog about SQL Server helping the environment and a suggestion on Connect to remove nonclustered indexes from backups. His thoughts were that we could

• Speed up backups
• Reduce disk (or tape) space
• Reduce energy usage

We'd also get corresponding quicker restore times, though you would then have to rebuild all your indexes. However your data would be available, and you would be able to build those indexes used heavily first, speeding up systems, and then backfill other indexes as you had time.

The other great situation here is that you could quickly move over other copies of the database for testing, development, reporting, etc., that might have much different indexing strategies.

In large systems, indexes can easily take up to 50% of the space of the data. Perhaps more if you heavily index. Shrinking these sizes would definitely help conserve resources, especially time. There would potential issues for people restoring that might need to set other expectations and manage their restore process more closely, but those could be handled.

The suggestion was closed by MS with a note that it was a complex problem, they'd have to redo backup, possibly slow it down, and it wasn't worth it. I'm not sure I understand that, since you would just grab the same pages, however not sending through pages that were indexes. Aren't these segregated out anyway?

Jonathan Kehayias had a great suggestion to have SQL Server automatically separate out NC indexes from data in filegroups. That would preserve the existing structure, and I think that's great. Sure you can do that today, but we want SQL Server to make things easier to manage, not more complex. Having the server separate out NS indexes by default, and then not back them up, would be a great way to improve the operation of the system.