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 '19 at 16:15

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, privacy policy and cookie policy

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