Index deletion and Backup size

If you delete 40GB of indexes, then certain backups will be smaller, such as:

• Full
• File
• Filegroup

Some backups won't generally be smaller, such as:

• Log

Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.

Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.

However, overall, the answer is "Yes, it should be smaller".

YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.

However - If you need to re-create that index then you aren't really buying yourself anything.

Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?