How to exclude indexes from backups in SQL Server 2008

If you switch over to full recovery mode, you can do this with filegroups, but it's really, really clumsy. You leave the data in the primary filegroup, and put the indexes in a separate (non-default, that's the key) filegroup.

Then you stagger your backups so that you're doing filegroup backups of the primary every night, and transaction log backups every X minutes.

When disaster strikes, you restore the primary filegroup by itself. The data is suddenly online, but the indexes are not. However, to get back to normalcy, you'll need to export that data into a new clean database and add indexes from there. You can't bring the database completely online without restoring all of the filegroups, and you can't say "I don't need that other filegroup anymore anyway."

For more about how this works, check out my video tutorial on filegroup restores.

Honestly, you really don't want to do this, even if you overcome the other issues others raise here.

When you restore the backup in an emergency, you don't want to wait for the indexes to rebuild, and you're going to suffer abominable performance until you do.

I can't think of a situation where you'd want to restore a backup without indexes, so in all cases you'll really want to back them up at the same time.

You'll likely need to look for other solutions to this problem...

-Adam

It sounds as if this isn't supported. From this bug report info:

There's been a lot of interest in this one, so I'll go into a bit more detail as to what is happening behind the scenes, and what it would mean to implement this functionality. Some types of index pages are segregated into separate allocation units, while others are mixed in with the data pages. Where we currently only look at the allocation bitmap to see if an extent is allocated, now we would have to go in and interpret what is stored in each allocation unit. Further, we would now not be able to just do a linear scan of the data files copying data, we'd be skipping around in the file. All of this interpretation of the data structures would drastically slow down backup. Restore gets even more interesting, because there are a lots of structures that would have to ba fixed up to account for the holes in the backup. Otherwise you'd have allocation maps pointing to pages which weren't backed up, and so have garbage in them, etc. etc. So, implementing this would mean that we'd save less data, take longer doing it, and take much longer restoring it. The other facet to consider is that this would take a large amount of engineering effort to get it all right. While that's not your problem on the surface, consider that it means that other features you may want to see wouldn't get built.