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

  • 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

    Tuesday, January 17, 2012 11:10 AM

Answers

  • 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

    • Marked as answer by Kalman Toth Monday, January 23, 2012 12:46 PM
    Wednesday, January 18, 2012 10:34 AM

All replies

  • 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!

    Tuesday, January 17, 2012 11:22 AM
  • 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
    Tuesday, January 17, 2012 2:46 PM
  • 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
    Tuesday, January 17, 2012 2:52 PM
  • 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
    Tuesday, January 17, 2012 7:20 PM
  • 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!

    Tuesday, January 17, 2012 8:43 PM
  • 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
    Tuesday, January 17, 2012 9:09 PM
  • 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
    Tuesday, January 17, 2012 9:12 PM
  • 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!

    Tuesday, January 17, 2012 11:52 PM
  • 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

    Wednesday, January 18, 2012 5:22 AM
  • 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

    • Marked as answer by Kalman Toth Monday, January 23, 2012 12:46 PM
    Wednesday, January 18, 2012 10:34 AM
  • Is it just me or does that stored procedure not work when the primary key is a clustered index?
    Tuesday, August 21, 2012 3:30 PM
  • 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.

    Saturday, September 15, 2012 3:51 AM
  • 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


    Thursday, May 23, 2013 6:06 AM
  •   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

    Tuesday, March 25, 2014 1:56 PM
  • 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.

    Wednesday, May 27, 2015 6:29 PM