How to move data from Primary Data File to other data file without destroying replication

Question

I have one Database which is replicated using transactional replication. Now, I want do spare some data of that database to another partition, because my server running out of disk space.

I was trying to move file to another filegroup trough SSMS using GUI tolls by clicking design at table and at properties tab changing "Regular Data Space Specification" to "secondary". When I try to save desing of table I get error Cannot drop the table 'dbo.MYTABLE' because it is being used for replication.

Here are a few questions:

• Should I create data files at another partition in already existing filegroup "PRIMARY" or it is better to I open new filegroup and than in that new "secondary" filegroup to open new datafile?
• Is there other way to I tell SQL to save data to another file besides dropping the table?
• Is there way to I can temporarily exclude some tables from replication while I move them to another filegroup and then put them again in snapshot without having to reinitialize the subscriber ?

Answer 1

SSMS is creating a copy of the table, populating it, dropping the original. hence the error.

I'd create a new filegroup, rather then adding files to the primary filegroup because of how the files are used within a file group

You can move a table "in-situ" to a new filegroup by rebuilding the clustered index using CREATE INDEX .. DROP_EXISTING

CREATE NONCLUSTERED INDEX PK_Mytable
 ON dbo.Mytable(PKColumn)
 WITH (DROP_EXISTING = ON)
 ON NewFileGroup