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 |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-05-06 : 10:42:45
|
| I have a DB with multiple filegroups, and a file per file group:[Primary] DBName.mdf[Monday] Monday.mdf[Tuesday] Tuesday.mdf[Wednesday] Wednesday.mdf[Thursday] Thursday.mdf[Friday] Friday.mdf[Saturday] Saturday.mdf[Sunday] Sunday.mdfI want to change the above to the following:[Primary] DBName.mdf[Primary1] DBName1.ndf[Primary2] DBName2.ndf[Primary3] DBName3.ndfIs there a way to do this through TSQL or MMS, or is it a case of creating a new DB with the new structure, and transferring the data over?ThanksHearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-05-06 : 10:43:31
|
| Sorry, I meant to say that I want to change it to:[Primary] -- ONLY ONE FILEGROUPDBName.mdfDBName1.ndfDBName2.ndfDBName3.ndfHearty head pats |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-05-09 : 23:20:51
|
| You can backup and restore with your changed file name also. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-05-11 : 09:57:39
|
| 1) Create all the new files to hold the data from the other filegroups (see ALTER DATABASE)2) Transfer all tables to the PRIMARY filegroup. Many ways to do this. BCP, select into, etc.. The best way is going to depend on various factors (diskspace, downtime acceptability)3) Once all tables and indexes are moved to the primary filegroup, you can drop the extra files, then the filegroups. |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-05-11 : 11:48:03
|
| Thank you both for your replies. And apologies for not replying sooner.Hearty head pats |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-05-11 : 22:23:30
|
quote:
Originally posted by mcrowley
1) Create all the new files to hold the data from the other filegroups (see ALTER DATABASE)2) Transfer all tables to the PRIMARY filegroup. Many ways to do this. BCP, select into, etc.. The best way is going to depend on various factors (diskspace, downtime acceptability)3) Once all tables and indexes are moved to the primary filegroup, you can drop the extra files, then the filegroups.
Why take this pain? If this was issue, I would simply restore the backup with changed configuration in maintenance window? |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-05-13 : 10:29:31
|
| You can't eliminate filegroups with a restore. You can move files around, but you can not modify filegroups. |
|
|
|
|
|
|
|
|
How to change File/Filegroup structure in DB?