Post reply

Interesting Move : Moving data to new FILEGROUP from older one

  • May 28, 2009 at 5:39 am

    #95581

    Hi ,

    In one of my database , i have around 400 tables on one of User defined FILEGROUP. now we have created a new Filegroup and we want to move around 200 tables to newer one.

    There is one way :

    create temp tables on new filegroup and move data to new table and then rename newer one with original name. but, this is very time consuming and also there are a lot of constraints ( PK, foreign keys) which are difficult to handle.

    Another way is:

    Recreate the clustered index on new filegroup but this is also not possible because all tables doesnt have clustered index.

    can somebody tell me any short method ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • May 28, 2009 at 6:45 am

    #1001354

    The first link below explains the code that can be used which you might be already aware,the second answer your questions.

    http://www.mssqltips.com/tip.asp?tip=1112

    http://my.advisor.com/doc/16671

    Hope it helps 🙂

    Cheer Satish 🙂

  • May 28, 2009 at 7:18 am

    #1001383

    bhuvnesh.dogra (5/28/2009)

    can somebody tell me any short method ?

    You've got the two ways to move tables. There isn't a short cut.

    For tables without a cluster you could always build a clustered index on that table on the filgroup that you want to move it to, then drop the cluster afterwards.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

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