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!
