Backup and restore manipulate the data files directly, without going through the buffer pool, which is one reason why this cannot be done. Rebuilding an index (or anything like it) is a logical operation, which must be logged. even though only a single page from an 8-page extent is allocated, it will backup the entire 64K extent), and it does it in physical order. It reads all extents containing allocated pages (i.e. The question is slightly different (I was asking if a restore can rebuild fragmented indexes), but this answer (from a former member of the MS SQL engine team) applies directly to your situation:Ī backup is a physical operation, not a logical operation. You can also read here for another way to accomplish this using filegroups, but again, this wouldn't be hit or miss as you've reported. This would not be a hit or miss situation as you've reported.

It's also possible, but I would expect you'd know if this is the case, that the backup was actually created on another server that had a copy of the database, and the backup job drops indexes first to make the backup smaller. So you'd have a timing issue where sometimes the backup has the indexes, and sometimes it doesn't. If it were such an ETL job doing this, for example, some loads may be taking so long that they aren't complete when the backup ends. Look for other types of activity that occur on the server leading up to the backup window. There may be an ETL job that removes indexes before a data load, and then creates the indexes again after the load is complete. It's most probable that the indexes did not exist when the backup completed. Such a feature would be nice as it would result in much smaller backups, but Microsoft states that there are many hurdles to adding such a feature (see here). There is no setting that allows indexes to be omitted from the backups or restores.

Index of backup