My Sceanario is like this. 1. we have sql server 2008 r2. 2. DataBase is very large like 1TB size. 3. Table (TblFact) is partitioned by Date 4. We have month wise FileGroup and DataFile (i.e. FG_201301 , FG_201301_DATA.ndf) 5. we have made read only to all month of 2012 file group. 6. we want add new column to this table. 7. how to proceed. 8. what will happened to read only filegroup data. 9. any pre-caution for above process. 10. we have taken filegroup backup to all month 2012. 11. For example, if we want to restore old bakeup of FG_201201 data then how to do with out affecting with new column data. we highly appreciate your help.
If you have to modify the structure, you'll have to take that filegroup out of read only mode in order to get the new column added there. Just because partitioning allows you to separate the storage of the table into discrete segments, you're still dealing with a single table. If you restore an older version of that filegroup that does not have the new column, then that column will go away. You will need to establish a new backup after adding the column. Once you are done, you can again mark that filegroup read only.