how to migrate certain filegroups with partitions to another server RRS feed

  • Question

  • Hi guys,

    I have 3 partition tables on multiple file groups on monthly basis., each month it generates about 1-2TB data on one partition, one filegroup. For more than 1 month old data, we set the filegroup to read-only.

    Now the question comes, disk space is nearly full, I would like to move more than 1 year old data another server, and will repeat the same job each month, we will need to query the old data, just not that frequently.

    I thought I can backup filegroup and restore filegroup, but apparently that won't work, as piecemeal restore is one time off thing, you cannot incrementally restore new filegroups.

    The only solution I can think now is using partition-switch on the old data filegroup(sure need to change the filegroup to writeable), then BCP or transfer data through linked server, then drop table, and since filegroup cannot be deleted, I have to shrink the data file to smallest.

    Do you have better solution? thanks!

    Thursday, September 3, 2020 1:53 AM