Sql-server – SQL Server – Move data from secondary ndf file to primary mdf file

sql server

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);  
GO  

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

But files are in different file group, will above works

Any suggestions

Best Answer

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.

Related Question