Answered by:
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
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: @SQLRichTuesday, 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- Edited by ElDoroCapitano Tuesday, January 17, 2012 2:52 PM
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-JanTuesday, 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-JanTuesday, 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 blogTuesday, 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
- Edited by Shivendoo Kumar Dubey Wednesday, March 26, 2014 1:37 AM
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