SQL Server MVP Deep Dives

Chapter 33. Efficient backups without indexes

Greg Linwood

As databases grow in size, the challenges associated with managing correspondingly larger backups also increase, as they require proportionally more storage space and processing time to complete.

The practice of compressing database backups has emerged over recent years to address these problems, but another opportunity still remains that can further reduce backup time, space, and energy consumption—simply by eliminating index data from backups! Because the proportion of data consumed by nonclustered indexes is significant in many databases (often 50 percent or greater), the savings to be made from eliminating these indexes from backups is also significant in many cases.

In this chapter, ...