3

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?

0

2 Answers 2

6

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.

6
  • "It's most probable that the indexes did not exist when the backup was started." Completed. Backups are restored to the point in time where the backup completes. Dec 7, 2017 at 14:53
  • I'm curious as to why indices should bloat backups. Do I understand correctly, based on the feature request you linked, that SQL actually backs up the content of indices (possibly many GB each), rather than just the fact of their existence (< 1 KB each)? Dec 7, 2017 at 16:22
  • @JonofAllTrades Indexes aren't just pointers, they contain an ordered list of the indexed columns, and a non-ordered list of any additional columns set to 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).
    – BradC
    Dec 7, 2017 at 16:37
  • @BradC: I'm aware of how indices work, but I assumed (never having given it much thought) that a backup just stored data and metadata, since by definition indices can be recreated from that data. Why store information twice? One more reason to trim unused indices, I suppose. Dec 7, 2017 at 16:50
  • @JonofAllTrades Yep, unnecessary indexes can definitely be a space hog. Use sp_spaceused [tablename] and it breaks out the exact data size vs index size of the given table.
    – BradC
    Dec 7, 2017 at 16:59
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.