We have a legacy SQL Server 2005 DB that is 8 TB in size. We will be purging data older than 5 years from this DB. After the purge process is complete, I want to be able to shrink the DB or reduce the file size of the DB.
I know shrinking (DBCC shrink..) is not a good option. Hence, was thinking of exporting all tables/objects/data via a SSIS package to a new DB so I can reclaim all the space. Is there any other way of reducing the size of mdf and ndf files?
Alternatively, I could try exporting all data to a new filegroup and deleting the original filegroup. However, most of the tables are heaps and don't have a clustered index. Is it possible to move all data/tables to another filegroup without a clustered index? Also, can I delete the primary filegroup if I move all the data to a different filegroup?