SQL Server 2016 - We have two files - one mdf on Primary and one ndf on Secondary filegroup.

We want to move data from secondary to primary and then delete secondary:

Approach 1:

Create clustered index on table which are on secondary and setting location of index to primary (Question: is clustered index only option or non clustered index will work too)

Approach 2:

DBCC SHRINKFILE (nameofdatafile, EMPTYFILE);  

-- Remove the data file from the database.  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE Test1data;  

But files are in different file group, will above works

Any suggestions

  • You have not only one but two approaches. They seem to be easy tests to run in a dev environment. Have you tried to execute them on a dev environment? If not, I advise you to do so ;)
    – Ronaldo
    Nov 25, 2019 at 16:15

1 Answer 1


If the datafile are in different filegroup the approach number 2 won't work, it will only work on the same filegroup.

For the Approach 1, please check: https://stackoverflow.com/questions/2438019/how-i-can-move-table-to-another-filegroup/31351806

I wouldn't recommend to move data back to primary filegroup, this should be only used for system objects and should be kept small for in case where you need to do piece meal recovery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.