If I delete some huge non-clustered indexes will the backup size of that database also decrease?
I am facing issue with huge backup files and don't have enough disk space to hold those backups.
I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?
If you delete 40GB of indexes, then certain backups will be smaller, such as:
Some backups won't generally be smaller, such as:
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?
