Post reply

Change Table Filegroups on 2005

  • August 13, 2007 at 1:46 pm

    #180049

    Change Table Filegroups on 2005
     
    I have approximately 400 - 600 tables that need to have their filegroups changed on a new SQL 2005 installation. In 2000, I could go the table properties in Enterprise Manager and change the filegroup, but on 2005 Management Studio, I can view (but not change) the table properties.
     
    I realize that I can create the table on the other filegroup using a new name, move my constraints and indexes to this new table, copy the data over, drop the original table, then rename the new table to the name my application is expecting. But that could take me weeks (especially with such a time-consuming process)! I don't mind the process being so slow, but I do mind it being labor intensive.
     
    Does anyone have an idea how I can automate this process (or at least make it as easy as it was with Enterprise Manager?)

  • August 13, 2007 at 2:01 pm

    #726603

    Move the clustered index into the new group... AFAIK, all the rest will follow along with it.

  • August 13, 2007 at 2:07 pm

    #726605

    I will do that, thanks!

  • August 13, 2007 at 2:14 pm

    #726607

    Assuming the previous idea works (make sure you test first).

     

    Script all the tables but only the clustered index.

    Do a search and replace to set the new filegroup and add the WITH DROP EXISTING option.

    That should be pretty much all you have to do... except wait for the code to complete its run.

  • August 13, 2007 at 3:50 pm

    #726621

    I ran into a similar situation and one of my co-workers finally found the screen in 2005 MS where you can simply change the filegroup and save the change like you could in EM.

    Once you have the table open in the modify screen press the [F4] key to bring up the table properties. From there you can change the file group and save the table into the new filegroup.

    Now since you have so many tables you could then use the change script as a basis to script this process out - but you will need to take into consideration the constraints and clustered indexes, etc.

    Hope this helps.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply