Help with Moving Filegroups - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Moving Filegroups

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-12 : 13:45:35

Good afternoon

Once again i'm back to ask the experts for some assistance.
(I've looked in the Books online) and only found a manual
"one-by-one" solution which would take all week if not all month to do. Perhaps you Guys/Gals can offer a faster more efficient way

I have a Database with 2 Filegroups (Primary) & (Data Filegroup 1)
I need to move all of the Tables/Indexes in (Data Filegroup 1) to the (Primary) Filegroup & Drop (Data Filegroup 1)

I hope you can provide a quick efficient way to do this other than the Books Online method, which notes you have to go to each tabke one-by-one and change the Filegroup.

Your help as always is greatly appreciated

Thanks
E

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-12 : 14:49:16

Why you need to move tables and indexes to Primary FG? Also all system resources are stored primary FG by default?

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-12 : 14:54:33

I have lots of tables in (Data Filegroup 1) which has grown large and created an additional data file
(that I now have to be concerned about when it comes to archiving/maintenance etc etc)
given this, i'd like to have just one single Filegroup (where all tables/indexes) are in the Default Filegroup (Primary)
one single Data file

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-12 : 15:08:58

http://www.sqljunkies.ddj.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2008-09-12 : 15:17:26

Hi sodeep

Thanks for your responce and the link. However this is the manual "one-by-one" method
i found in BOL. In which I would have to use this method to move each table & index
one-by-one to the destination Filegroup. and I have lots of them to move

I was looking for a possible T-SQL way to move ALL tables/indexes from one Filegroup to another
in one quick step

E

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-12 : 15:37:37

quote:
Originally posted by pharoah35

I have lots of tables in (Data Filegroup 1) which has grown large and created an additional data file
(that I now have to be concerned about when it comes to archiving/maintenance etc etc)
given this, i'd like to have just one single Filegroup (where all tables/indexes) are in the Default Filegroup (Primary)
one single Data file

You said you have a maintenance issue because the files in the Data Filegroup 1, so I don't understand why moving all these tables into the Primary filegroup could possibly make you situation any better. More than likely, it will just create additional problems.

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-13 : 23:35:09

You will get performance issue with big databases if you do that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources