How to transfer the tables from one file group to another file group in SQL 2008.?

Question

Hello all,

I have few issues regarding the transfer of the tables from one file group to another file group  in SQL 2008 and also How can we  backup and restore the particular database based on file group level.

Let’s say I have a tables stored within the different FG. such as

Tables                                                                                                                                 File group

Dimension tables                                                                                                                Primary

Fact tables                                                                                                                          FG1

  ...                                                                                                                                      FG2…

zzz_tables                                                                                                                          DEFAULT_FG    

dim.table1                                                                                                                          DEFAULT_FG

dim.table2                                                                                                                          DEFAULT_FG

…                                                                                                                                             ….

Here all I want to transfer the dim.table1 ,dim.table2  from  DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2  from one FG to another .I have tried somewhat but I couldn’t get the exact way .So if someone have better idea please share your knowledge that would be really appreciated.

Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing  the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?

Hope to hear from the one who knows better approach for this kind of task .Your simple help will be much appreciated.

Regards,

Anil Maharjan

Answer 1

Hi,

Check out this article for moving tables between filegroups - http://www.sqlservercentral.com/articles/Administration/64275/

Once you have moved the data out of the DEFAULT_FG do you not want to back it up or restore it because the data will not change? If that is the case make that filegroup read only and back it up only once in a while.

http://msdn.microsoft.com/en-us/library/ms179401.aspx

http://support.microsoft.com/kb/281122

http://sqlserverpedia.com/wiki/Db_Config_-_Read-only_File_Groups

 

 

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Can I ask the reason for the move? Ideally your primary filegroup should just contain system metadata.

---

If you find this helpful, please mark the post as helpful,
If you think this solves the problem, please propose or mark it an an answer.

Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

Richard Douglas
My Blog: Http://SQL.RichardDouglas.co.uk
Twitter: @SQLRich

Answer 3

Hey there, like Richard say "your primary filegroup should just contain system metadata",100% 100% right!!

if you like to move your data into different filegroups then you should recreate your clusterd index with your new prefert FileGroup.

 

 

IF  EXISTS (SELECT * FROM sys.indexes  
            WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Order Details]')  
            AND name = N'PK_Order_Details') ALTER TABLE [dbo].[Order Details] DROP CONSTRAINT [PK_Order_Details] GO 



ALTER TABLE [dbo].[Order Details]
ADD  CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED ( 
   [OrderID] ASC, 
   [ProductID] ASC
)WITH (ONLINE = OFF) ON [OrderDetailsFG] GO  

 

---

Mark as Answer button if a post helps you to solve the problem

Answer 4

Ideally your primary filegroup should just contain system metadata.

Can you give more information (or references) why you believe the primary filegroup should not contain data or indexes?

Thanks.

---

Gert-Jan

Answer 5

One of the main reasons I choose not to populate the primary FG with data is so that I can bring the DB online faster using partial restores. The DB can only come online once the primary filegroup has completed recovery. The larger this is the longer this can take.

http://sqlserverpedia.com/wiki/Restoring_File/Filegroup_Backups

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!

Answer 6

Thanks. That is a good reason in a certain class of situations.

As a general statement it is a bit weak.

Because you can only take advantage of this faster recovery if you actually have several filegroups, and some (or one) of those filegroups don't need recovery, or is done very quickly. And it very much depends on the situation whether the database is useful before all filegroups are recovered. If you need all your filegroups to be healthy before your application can run, then this approach has no advantage.

The tradeoff is, that this approach requires you to create several filegroups. On bigger systems that is probably a good idea and not a problem at all. On small systems, it can have a negative impact on performance, especially if the filegroups are not properly dimensioned.

 

---

Gert-Jan

Answer 7

I think this blog post 

SQL Server Filegroups: The What, The Why and The How 

and the followups such as

WiSSUG "Filegroups: Putting the Pieces Together" Materials

may be helpful.

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Totally agree that this is a big it depends - just wanted to put an example there for the asker. As you stated if you are going to have the overhead of managing multiple filegroups the I generally don't want to be dumping data into the primary FG but my decisions change with the requirements and circumstances.

 

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!

Answer 9

Thanks to all ,

I really appreciated your response and help , Currently I have no any clustered index defined within the dim.table1 ,dim.table2 and so on.

Cannot we transfer the tables from DEFAULT_FG to another FG without having clustered index on that tables or without select/insert operation so that I couldn’t have to recreate the tables  L. I have researched somewhat and found that we couldn’t  move the tables without having the clustered index to another file group simply in MS-SQL so sad L..!!!

Hope to hear from you guys more and thanks really for your responses.

Regards,

Anil Maharjan

Answer 10

Well after all my full day research on this topic had paid off, I finally got the solution and am so happy to research on these things. It makes us feel really happy after all our research and hard work doesn't goes as waste.

Finally I got what I am looking for and want to make sure that I am able to transfer the tables from DEFAULT_FG to another FG without tables having clustered index on that tables .

With the help of the link below I finally got my solution where Roberto’s coded store procedure simply works for this.

Really thanks to him for his great post and thanks to all for your response and your valuable time.

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Regards,

Anil Maharjan

Answer 11

Is it just me or does that stored procedure not work when the primary key is a clustered index?

Answer 12

Doesn't work, I'm getting for all tables:

Msg 468, Level 16, State 9, Procedure SP_MOVE_TABLES, Line 590
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Answer 13

Hi,

To Move the table from one file group to another you can Drop the existing Cluster Index if you have any & then Re-Created the Cluster Index on New File group.

If you don't have cluster Index on a table then just Create a new Cluster index on new File Group & Then Drop It.

This will move the data from one file group to another file group for a table.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-filegroups-the-what

How Do I Move An Object to a Different Filegroup?

You can move a table from one filegroup to another, provided the table has a clustered index on it.

Note: You can move a heap (a table with no clustered index). To do so, you would create an index, move it, and drop the index.

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 14

  I used to follow below steps to move table from one file group to another file group :

1. If table has Clustered index on primary key :

   step-1: then drop all references then drop the primary key

   step-2: recreate the primary key with clustered on new file group.

    step-3 : recreate all the references.

2. If table is heap.

   step-1: dump all data in another temp table using select * into,

  step-2: drop table and recreate table in new File group

   step-3: bring back data from temp table.

3. if table has clustered index on other than primary key:

 step-1: drop clustered index

 step-2: recreate clustered index in new file group

---

Regards Vikas Pathak

Answer 15

Another article, on a publicly available site:

http://stackoverflow.com/questions/2438019/how-i-can-move-table-to-another-filegroup

Basically, change the storage on the clustered index; most times this is the PK_ (primary key) index. This will move the data of the table. You can move your other indices as well.