Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.
Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
sql

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>