How do you move a DataFile to another FileGroup

Question

Post reply

How do you move a DataFile to another FileGroup

Mark Bosomworth

Say Hey Kid

Points: 669
More actions
May 10, 2007 at 7:47 am

#73188

I have 2 Data Files both in the PRIMARY FileGroup. How can I move one of them into another FileGroup?

Viewing 5 posts - 1 through 4 (of 4 total)

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

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

Use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause:

USE UserDB

go

DBCC SHRINKFILE ('LogicalFileName',EMPTYFILE)

Then delete the file using

USE master

go

ALTER DATABASE [DatabaseName] REMOVE FILE [LogicalFileName]

go

Then recreate the file on the new file group:

USE master

go

ALTER DATABASE [DatabaseName] ADD FILE ( NAME = N'LogicalFileName', FILENAME = N'<Path>\PhysicalFileName.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FileGroupName]

go

James.

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 2

Thanks for that James, however I want to preserve the tables that are already in each DataFile. Doing a shrinkfile will move all the tables into one DataFile.

JeremyE SSCoach Points: 16199 More actions · Answer 3

SQL server writes data to the data files in a filegroup with a proportional fill algorithm. The data files with the most free space get written to before data files with less free space. Therefore the data for a table is written acrossed the number of data files you have in the filegroup and you cannot move data files between filegroups.

I would agree with James and also add that once you have created the new filegroup and data files for the group, you will need to move the desired tables to it.

kozloski SSC Eights! Points: 804 More actions · Answer 4

Alright. While both answers may be somewhat technically accurate, they aren't answers to your question, Mark.

You won't actually move the data file, but you can move specific objects that reside on the files in the primary filegroup. If you now have multiple files in the primary filegroup, the data will be distributed among them.

So, create a new filegroup, and add one or more files to that filegroup.

ALTER DATABASE foo ADD FILEGROUP NewFG1

ALTER DATABASE ADD FILE ... TO FILEGROUP NewFG1

Next, determine which tables or indexes you want to move to the new filegroup.

Actually moving the objects is usually as simple as rebuilding indexes.

To move a non-clustered index, rebuild it specifying the new filegroup as the location. I like to use the CREATE INDEX statement with the DROP_EXISTING option. You can simply script out the indexes in Enterprise Manager or Management Studio and modify the resulting statement.

CREATE INDEX nc_ExistingIndex1 ON table1 ( column1 ) WITH DROP_EXISTING ON NewFG1

To move a table itself, you can use the same process if a clustered index exists on the table already. ( If it is a heap, create a new clustered index on the table, specify the new filegroup, then you can either keep or drop the index afterward. )

Hope this helps!

-- J.Kozloski, MCDBA, MCITP