Is it possible to Auto Archive Old Data into Read Only partitions on a very large table with existing PS/PF but then also allow continued automatic pruning/dropping of those read only partitions i.e. dropping data after 3 years for GDPR...

So whilst I can see it is possible to a) auto archive old data into a read only filegroup (to backup once and save from normal backups going forward) and b) automatically drop partitions that fall out of set retention periods

I'm not sure that I can combine the two? It also seems you cannot switch data into a partition that isn't on the same FG (the proposed method for archival) - but equally if its in the same FG I can't make it read only(?) - Ideally it would automatically work out what date was 3yrs/2rs ago and alter the PS/PF to redirect that data into an archive file - either natively or post move made read only.

We have up to 3 CDR tables that share the same FG/FS. Partitioned by date, CCI, currently spread across 4 generic files. Controlled through an automated process to forward create partitions 0 and importantly here - drop partitions that go past retention period. Issue is these are huge tables and take forever in backups which they don't need to be in everyday - one backup of say 2018, and 2019 would be fine... but I'd need to maintain that auto dropping mechanism to comply with GDPR - so potentially need a daily level archive file that is then dropped? (Assuming can drop a read only parititon, and dynamically make today minus 2 years read only on a rolling basis?

Hope that makes sense - running on sql server 2017 btw.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.