Change file group structure of DB
-
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
-
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.
-
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.
-
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?
-
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://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=1526
Jonathan
-
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