SQL Server Performance Forum – Threads Archive

Moving tables from one file group to another

I want to move my large tables from default to a different file group. All i could do here to achieve this is to go to the tables design page and move it from there. I was wondering, Is there a script which can do this automatically? Thanks,
Cali

---

Will this help? Use Master
GO
–Lists the name of files to restore
RESTORE FILELISTONLY
FROM DISK = ‘Cocuments and SettingsMy DocumentsDatabaseNameDatabaseName.BAK’ –OR restore the database
RESTORE DATABASE DatabaseName
FROM DISK = ‘Cocuments and SettingsMy DocumentsDatabaseNameDatabaseName.BAK’
WITH NORECOVERY,
MOVE ‘DatabaseName_DATA’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQLDataDatabaseName.mdf’,
MOVE ‘DatabaseName_ix’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatabaseName_indexes.ndf’,
MOVE ‘DatabaseName_LOG’ TO ‘Crogram FilesMicrosoft SQL ServerMSSQLDataDatabaseName.ldf’
Name
———
Dilli Grg (1 row(s) affected)

---

Unfortunately this is restore script and this wont help me.

---

You can use Management Studio (or alter) and change the cluster index to the new file group. That is all. If you want, you can change no cluster index too.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

There are three ways you can achieve this….. 1. If you have the clustered index on the table use CREATE INDEX WITH DROP_EXISTING to create your clustered index to new file group which will automatically moves your table to the new group…
Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index 2. You can create a New table with with same schema on different filegroup and copy the data and rename the old table *_old and rename new table to original one and drop the *_old table after validating the data… 3. You can do this as Luis mentioned using SSMS but in the backupround it will also create a temp table and copy the data and rename the table… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thanks for the answers. Let’s say, if i dont have a clustered index on my tables? Does that mean, i have to choose between option 2 and 3? Isnt there a alter move command to move tables from one file group to another? Oracle has this and it’s very flexible. Thanks,
Cali

---

You can create one, when you are creating specify the filegroup to which you want to move the table…
Once created table will be moved to new group then you drop the clustered index if you don’t want…
OR
You can go for option 2 or 3…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

There’s also a possibility that if you don’t have a clustered index on said table, you’d want one (you just don’t know it yet) In general (there are exceptions ofc, though rare) a clustered index is a good thing to have on any given table. =;o)
/Kenneth

---

http://mssqltips.com/tip.asp?tip=1254
http://msdn2.microsoft.com/en-us/library/ms186342.aspx
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

As suggested by Mohammed this feature in SQL is far better than Oracle, as I speak to my Ora.DBAs here. SQL2005 is getting far better than previous ones.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>