November 27, 2002 at 10:32 am
I am trying to move a table from the primary filegroup to our userdata filegroup. I did find a way to do it manually through Enterprise Manager. Since we have a hundred or so tables, I would rather find a way to script it. I would appreciate any info or ideas you can offer!
Thanks,
John
November 27, 2002 at 11:13 am
Creata cursor that loops through the tables that you want to move.
Inside the loop recreate the clustered index of the table in question on your new filegroup. (The file group must already exist and have files added to it).
Nigel Moore
======================
December 1, 2002 at 8:42 pm
Unfortunatley no clean way to script the transfer of all permissions, constraints, keys and triggers. EM offers you the best solution or possible DMO could offer a quick and usefull solution but nothing i have tried yet.
December 8, 2002 at 3:13 pm
EM is doing your legwork for you. by the time you create and debug a script to move the tables, you can have it done in EM. take this as a lesson learned and use caution when creating tables.
One thing you might try is to script out all your tables using EM, edit the DDL to move the tables to where you want and restore into the new tables from a backup.
December 8, 2002 at 5:59 pm
I still have concerns over scripting out doing this. Here are the base steps EM follows minus a bit I know I am missing.
Collect Permissions
Collect Triggers
Collect Extended Properties
(other pieces I cannot remember)
Create new table based on changes to definition (your case putting on another filegroup)
Insert all data from old table to new table.
Delete old table
Rename new table to old name
Reapply triggers, permissions, extended properties, etc.
Most of this can best be seen by using profiler and doing this to a table.
It is just easier to use EM unless someone has all the details covered using DMOP or WMI. EM has it wrapped up.
December 8, 2002 at 6:01 pm
quote:
One thing you might try is to script out all your tables using EM, edit the DDL to move the tables to where you want and restore into the new tables from a backup.
I am confused by this as you cannot restore data into a different structure it will overwrite the files and the old struture will be back. You could BCP or DTS out then back in but that is further around than needs be.
December 16, 2002 at 3:44 am
Maybe I am a bit sleepy today or something but if just rebuild the clustered index on your new filegroup why would you need to script user permissions, constraints, etc.. after all you are just rebuilding the same table.
Nigel Moore
======================
December 16, 2002 at 4:14 am
quote:
Maybe I am a bit sleepy today or something but if just rebuild the clustered index on your new filegroup why would you need to script user permissions, constraints, etc.. after all you are just rebuilding the same table.
You would think but SQL does not support moving the table between filegroups directly. What it actually does behind the scenes (which you can watch it do with profiler) is create a new table and popukate it from the old data. When done it deletes the old table and renames the new. The problem with why you must have the constraints, triggers and other bits scripted first is that the delete of the old table removes them, so you have to add back when done (again you can watch EM do all of this in Profiler). I think constraints are done this way do insure the names remain the same, especially if the user created them with a specific name.
December 16, 2002 at 4:34 am
If tell SQL to rebuild your clustered index you don't lose your triggers. If you did everytime you rebuilt your index you would have to rebuild your triggers. I have just tried this using Northwind db on my laptop (SQL 7) and it worked ok, triggers all still present.
Nigel Moore
======================
December 16, 2002 at 5:24 am
Reindex is not the same as moving the table to a new filegroup and there is no parameter to reindex to another filegroup. Create a complex table and change between fielgroups to see what happens (Watch with Profiler).
December 16, 2002 at 5:33 am
Surely if you move the CLUSTERED index to a new filegroup then SQL will move the data as well as the clustered index as the leaf level of the clustered index is the data pages and not the keys!
"The data itself is part of the clustered index" -
Inside Microsoft SQL Server p406.
Nigel Moore
======================
December 16, 2002 at 5:36 am
CREATE UNIQUE CLUSTERED
INDEX [PK_Categories] ON [dbo].[Categories] ([CategoryID])
WITH
DROP_EXISTING
ON [Secondary]
[Secondary] <--- just change this to whatever filegroup
Nigel Moore
======================
December 16, 2002 at 5:42 am
If you do this and go to the properties of the table in EM it will say the table is on whatever filegrouo you created the new Clustered index. I know this works I am just after doing it.
Nigel Moore
======================
December 16, 2002 at 5:56 am
I see what you are saying. I tested and works perfectly (as long as you have a clustered index to use or can just make one then remove it, does not have to be unique). Odd, why the h$!! does EM not just do that if there is a clustered index. Oh well, guess they just love overkill. Unless maybe there is something this msises, but constraints and triggers do remain intact, did not try extended properties and other items.
December 16, 2002 at 6:02 am
I assume (dangerous I know) that if rebuilding clustered indexes caused any such problems then you would have heard about it because its such a common practise and I also assume that changing the filegroup would be no different then using the original. I shall test this all out and get back with the results.
Nigel Moore
======================
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply