It looks like at a client site, after a database backup and restore (from SQL Server 2008 to SQL Server 2012), it looks like they have lost indexes from the tables and we had to manually create them. We have seen two instances of this.
I thought backup and restore keeps all the objects intact. Is this possible? Is there a setting that actually lets users omit objects during a restore?
There is no setting that allows indexes to be omitted from the backups or restores. 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).
It's most probable that the indexes did not exist when the backup completed. There may be an ETL job that removes indexes before a data load, and then creates the indexes again after the load is complete. Look for other types of activity that occur on the server leading up to the backup window. 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. So you'd have a timing issue where sometimes the backup has the indexes, and sometimes it doesn't.
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. This would not be a hit or miss situation as you've reported.
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.
INCLUDE. So if you add a lot of wide columns to an index, it can use up a lot of storage. SQL Backups don't do any interpretation of database contents, they simply take raw file extents (not even data pages) and send directly to disk (with an optional compression step in there).
sp_spaceused [tablename] and it breaks out the exact data size vs index size of the given table.
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:
A backup is a physical operation, not a logical operation. It reads all extents containing allocated pages (i.e. 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.
Rebuilding an index (or anything like it) is a logical operation, which must be logged. Backup and restore manipulate the data files directly, without going through the buffer pool, which is one reason why this cannot be done. Another reason this cannot be done is that backup and restore have no understanding of what is contained in the data being backed up....
Bottom line - backup and restore are physical operations that never change the data.
So in your case (and consistent with Tony's answer), there are no possible settings for backups or restores that can ignore/drop/add/rebuild indexes, since those are all logical operations, which would have to be properly logged.
I concur with Tony that the most likely scenario is some sort of data load process that drops indexes to optimize import speed. Perhaps the nightly data load was interrupted or failed, or perhaps the full backup took place in the middle of the job after the indexes had been removed but before they were re-added.
