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 |
|
MuadDBA
628 Posts |
 Posted - 2002-03-20 : 13:28:50
|
| Hiya,I have a 4GB table that I want to move to another filegroup so I can back it up seperatrely from the rest of my 33GB database.Is there an easy way to do this (alter table change filegroup doesn't seem to work :) or am I stuck with selecting from one table and inserting into another?Thanks in advance,Joe |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-20 : 13:31:56
|
| You can drop the clustered index (if it has one), then use CREATE INDEX...CLUSTERED...ON (filegroup); since the clustered index physically orders the data it will move it to the other filegroup.If you will ever script out this table in the future, you should modify it so that the CREATE TABLE statement includes the ON (filegroup) clause in it. |
 |
|
|
MuadDBA
628 Posts |
Posted - 2002-03-20 : 13:36:05
|
| Thanks Rob! I hadn't thought of that, even though I don't have a clustered index, it might be worth it to create one for just this purpose.......oops, I just realized this will probably fill my transaction log. DRAT! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-20 : 13:50:05
|
| Creating an index does not fill the log with index keys (not in SQL 2000 anyway), it only logs that an index was created. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-03-20 : 14:35:47
|
quote:
Creating an index does not fill the log with index keys (not in SQL 2000 anyway), it only logs that an index was created.
Unless your database is in FULL recovery mode, in which case the log will grow quite a bit... You can always switch to BULK LOGGED mode though to keep the log size down while your index is being created...---------Strong SQL Developer wanted in the Boston area. Please e-mail if interested.Edited by - izaltsman on 03/20/2002 14:36:13 |
|
|
|
MuadDBA
628 Posts |
Posted - 2002-03-20 : 16:21:31
|
| DOH!Looks like my plan didn't go as I would have liked. I was moving this table to another filegroup as part of an archiving process. I was to backup just that filegroup, and then whack the table. Unfortunately, when I go to backup the filegroup, it tells me all the other filegroups in the DB are "subject to logical recovery" and so therefore must be included in the backup....I created this filegroup just yesterday, for this specific purpose only....DANG! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-20 : 16:25:33
|
| I believe that you have to perform a full database backup before any other kind, and also after a major structural change (like adding a filegroup). |
|
|
|
MuadDBA
628 Posts |
Posted - 2002-03-20 : 16:52:59
|
| This is actually pretty humorous....part of the reason I started this is because the DB grew to such a size that we could no longer back it up.So, looks like I am constrained to using a DTS package or creating the table in a completely different database.joy! |
|
|
|
|
|
|
|
|
Filegroups