# Just how to exclude indexes from back-ups in SQL Server 2008

Our every night complete (and also routine differential) back-ups are coming to be fairly huge, due primarily for indexes on our tables; about half the backup dimension is included indexes.

We're making use of the Simple recuperation version for our back-ups.

Exists any kind of means, via making use of FileGroups or a few other file-partitioning method, to exclude indexes from the back-ups?

It would certainly behave if this can be included full-text magazines, too.

0
2019-05-04 13:26:31
Source Share

could be an insane suggestion, yet below goes.

1. drop your non - gathered indexes that occupy great deals of room
2. do a backup
3. re - create the indexes you went down

Of training course you can just actually do this if you data source permits some down - time in the day.

Additionally, do not drop your gathered indexes as SQL Server will certainly throw away a great deal of time transforming these to a lot.

Does acquiring that added disk room feel like a less complicated remedy yet?

Have you taken into consideration doing compressed backups? this is a new attribute of 2008, it might be an alternative for you.

0
2019-05-11 20:17:32
Source

If you switch to complete recuperation setting, you can do this with filegroups, yet it's actually, actually awkward. You leave the information in the key filegroup, and also placed the indexes in a different (non - default, that's the key) filegroup.

After that you startle your back-ups to make sure that you're doing filegroup back-ups of the key every evening, and also purchase log back-ups every X mins.

When calamity strikes, you recover the key filegroup on its own. The information is instantly on-line, yet the indexes are not. Nonetheless, to return to normality, you'll require to export that information right into a new tidy data source and also add indexes from there. You can not bring the data source entirely online without recovering every one of the filegroups, and also you can not claim "I do not require that filegroup any longer anyhow."

For even more concerning just how this functions, look into my video tutorial on filegroup restores.

0
2019-05-08 23:26:11
Source

Honestly, you actually do not intend to do this, also if you get over the various other concerns others raise below.

When you recover the backup in an emergency situation, you do not intend to await the indexes to restore, and also you're mosting likely to endure awful efficiency till you do.

I can not consider a scenario where you would certainly intend to recover a backup without indexes, so in all instances you'll actually intend to back them up at the very same time.

You'll likely require to seek various other remedies to this trouble ...