SQL Server Performance

move data from a partitioned table to a different file group

Discussion in 'SQL Server 2005 General DBA Questions' started by suba, Mar 15, 2010.

  1. suba New Member

    Hi... can please someone tell me if there is a way to move data from a partitioned table to a table in a different filegroup.
    My table is on a daily partition (based on date) and we regularly want to move data from the table to a different filegroup.
    Is this possible in sql server 2005. if yes, please let me know the steps that needs to be done.
    will switch partition help me in this regard? it seem to give out a error that the destination table is on a different file group.
    suba
  2. arunyadav New Member

    Moving data to another filegroup requires data movement. One method is to MERGE the partition you want to move, set the next used to the new filegroup and then SPLIT to move the partition to the desired filegroup. The partition scheme or filegroup must already exist.
    Assuming aligned indexes, a faster method would be to copy the partition data to the new filegroup with a minimally logged SELECT ...INTO, create a check constraint to match the partition boundaries and create indexes. Finally, SWITCH out the old data into a staging table and SWITCH in the newly created table.
  3. suba New Member

    i want to move the old data to a new file group and keep the recent data in the primary file group. but the above will move the recent data to the new file group. is there a way to do it?

Share This Page