Chapter 33. Efficient backups without indexes
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, we’ll investigate a practical method to reduce backup sizes and timing—by separating tables and indexes into different filegroups, and then using filegroup backups instead of full database backups to eliminate index data from routine backups.
The example scripts shown in this chapter have been tested on SQL Server 2005 and SQL Server 2008, but simple customizations could be applied to allow this technique to also be used with older versions.