filegroupe

Question

Post reply

filegroupe

a.e.ghorbani

Right there with Babe

Points: 741
More actions
January 1, 2011 at 6:20 am

#231639

i have 2300 tables in my database.all of tables have relation together.i want to move tables to another filegroupe whitout remove my relation or without any change .only i want to change my filegroupe.and i want to change those table that start with " like Crm%" move to "CrmFileGroupe"
please advise me

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Recreating the Clustered Index on the new file group will move the table as well.

CREATE CLUSTERED INDEX ..... ON [newfilegroupname]

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

WayneS SSC Guru Points: 95461 More actions · Answer 2

a.e.ghorbani (1/1/2011)
i have 2300 tables in my database.all of tables have relation together.i want to move tables to another filegroupe whitout remove my relation or without any change .only i want to change my filegroupe.and i want to change those table that start with " like Crm%" move to "CrmFileGroupe"
please advise me

Was there anything wrong with the answer that was given you just a couple of days ago here?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

a.e.ghorbani Right there with Babe Points: 741 More actions · Answer 3

no , thats true . thanks. but when i run it , it had many error with forign keys and i had to remove all of my forign keys and now i dont know what do i do?but that was a back up of my database.

please advise me what do I do ? I have a database with many tables that have many relation and i want to change filegroupe?what do I do exactly?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

First disable the Foreign keys http://sqlserverpedia.com/blog/transact-sql-t-sql/how-can-i-disable-all-the-foreign-key-constraints-defined-for-a-database/ and do the rest of work

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Script each foreign key before you drop it (you will need to drop them, disabling is not sufficient). Then, once you've moved all the tables you'll have the scripts that you need to recreate the foreign keys.

You can use a similar script to the one Wayne gave you previously (just using sys.foreign_keys and sys.foreign_key_columns) to generate all the scripts to recreate the foreign keys.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 6

maybe even start with this script that can help you with your inventory and scripts.

Just add that part to use the new location.

http://www.sqlservercentral.com/Forums/FindPost368929.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

a.e.ghorbani Right there with Babe Points: 741 More actions · Answer 7

thanks

i recreate all forign keys with compare 8 in red gate ssoftware

i successfull .thanks

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 8

Hey... you have compare 8 !

Just script your full db.

Edit the sqltext and alter all current filegroup to NEWfilegroup.

Save the file

Use compare8 to generate your migration script comparing the db with the script :w00t: