Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2006-11-17 : 10:11:58
|
| I have a unique situation. Somebody had created about 9 file groups in one our databases, say filegroup1, 2....and so on. Each of these at most has one table. The rest are housed in the primary filegroup.I want to get rid of all these file groups and have just one.I am not sure if there is any other way but to script the tables/indexes and then drop them, and recreate them on Primary file group...Has anybody every tried that, successfully? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-17 : 12:45:04
|
| Firstly, it's actualyl a good idea to put your data in a filegroup separate from the primary filegroup even if you don't want to split across multiple filegroups like you have it now. So I'd say if you decide to go ahead with consolidating everything, then keep one of the filegroups and move everything there, so none of your data is in the primary filegroup (reason for this is that then the system tables in the primary filegroup never content with your data in event of the filegroup running out of space etc.)Now, as far as moving tables goes, it depends on whether or not you currently have a clustered index. When you create a clustered index you can specify the filegroup for the index, and since a clustered index is the table, you are effectively moving the table when you create a clustered index on a different filegroup. If you already have a clustered index then just use CREATE INDEX with the DROP_EXISTING clause to move it to a different filegroup. There is also an example in Books Online that shows doing it with the ALTER TABLE statement, which allows you to move the table by dropping the primary key constraint (of course you'd then add it back) - see example N. for the ALTER TABLE statement. |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2006-11-17 : 12:53:39
|
| Thank you, I thought as much...Albeit, I am not sure how open the users are for playing with the tables. I obviously cannot do anything with a restore can I? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-17 : 16:47:44
|
| No, you can restore selected filegroups (and that may be a reason why so many filegroups were created in the first place, so you may need to consider that before you make changes), but you cannot restore multiple filegroups into a single one. |
|
|
|
|
|
|
|
|
Dropping Multiple FileGroups from a SQL2005 DB