question

haresh.makwana avatar image
haresh.makwana asked

SQL 2008 Table Partitioning (sql 2008r2)

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.
partitioning
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.