Post reply

Change file group structure of DB

  • November 7, 2006 at 2:38 pm

    #115038

    I need to change the file group structure of an existing DB that is currently spread over 4 file groups.

    I want to pull all the files into one group "Primary", or in other words the default single file group.

    The DB is not a production DB so I have freedom to do pretty much whatever is required to accomplish this.

    So far all my attempts (create new DB and restore backup, script all objects and run in new DB etc.) have failed.

    Can this be done?

    Thank you!

    JM

     

  • November 8, 2006 at 9:29 am

    #670624

    Try this from SQL Junkies. It's written for SQL 2000, but works as well on SQL 2005.

    http://www.sqljunkies.com/howto/b9f7f302-964a-4825-9246-6143a8681900.scuk

    -S.

  • November 8, 2006 at 9:33 am

    #670628

    If there aren't too many tables you can use Ent Mgr: go to table, right click, design, then use the manage relantionships button to pick a new filegroup.  Bear in mind its moving data and could take a while for big tables, indexes will get rebuilt.  As its dev it shouldnt be an issue for you by the sounds of it.  If this was live it could be more problematic! 

    A guy called Rod Merritt posted a script on here to do this a while back, you may want to search the site to see if you can find it.

  • November 8, 2006 at 9:46 am

    #670634

    Sorry didn't realise I was in the 2005 forum

    Dont really use 2005 yet, have a look at Alter Table it seems to be able to move tables with clustered indexes to another filegroup?

  • November 8, 2006 at 9:56 am

    #670639

    Thanks all for you replys.

    I found this script by Omri Bahati this morning and have been playing with it.  It seems to be what I need.  It is a very impressive piece of coding!

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1526

    Jonathan

  • November 8, 2006 at 10:05 am

    #670641

    Thats useful!

    The Ent Mgr equiv seems to be :-

    Select the table in Studio Mgr

    Click View (at top)

    Click Properties window

    This Brings up a separate 'designer info' window where you can alter the filegroup

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

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