We have a data warehouse with about 4TB worth of datafiles. A couple of these databases consist of partitioned tables.
Since we're running out of disk space (on premise environment), we are looking to remove old data and archive some of it too. But there is a lot of allocated free space in the data files.
I'm looking for a way to get rid of this free space so we can migrate to a newer version (currently 2008 R2).
What I've read is that making a compressed FULL backup and restoring this on the new server will also remove this allocated space. Unfortunately I have not been able to find information to verify this method yet. Does anybody know if this holds true or perhaps any of you know a better way?
The issue is that a new partition is created each month. All the older partitions are just historic data. So logically SQL Server will never utilize free space in these older partitions.
We are talking about a thousand files in total so I'm looking for something automated or time saving. Shrinking could end up taking weeks, providing we don't want to cause any load during production hours.