We have 2 servers:
- Server A. production (no partitions)
- Server B. New Server (partitions and compression)
We restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy.
Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.
We cannot query any old data on the new server, only data from when we turned on replication.
The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, it's using the
PRIMARY filegroup (same as the current live server is using).
On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.
It appears turning on replication replicated the filegroup side of things. Is there a way to point the table back to partitions and save all the work we did getting it ready for switch over?
It's a pretty large database. This one table is 11TB on the production server.
SQL Server 2017 Enterprise Edition.