I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may change on occasion, so I am not going to mark any file or file group as read only.
Will I still be able to do a partial backup on just the files I choose without marking any files as read only? Or is it required that the files I want to skip be set to read only?
Now, if I have to mark the old data partitions as read only - if an old record is updated some time in the future, including the field that is used to assign it to a partition, would SQL server move the record to the read write partition, or would this update fail? (I know, read only means read only, but what would happen in this scenario?). In this instance, perhaps I would just add the record to the read write partition, and search for the latest version of the record (max modificationdate etc..)
Out of one million records, I would expect only 20,000 new or updated records per year. Only 200k of the records are "current".
I'm looking to only have to regularly back up the read /write files, and performing less frequent backup of the old data.
This is a SQL Server 2016 installation (Standard Edition).