Click here to Skip to main content
16,007,126 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

Two days back i have attended one interview, They asked one question that is,

Q) My database having two ndf files (SQL Server 2005 Database), now i want to delete
one ndf file. Before that obviously we need to move the data from one ndf to
another ndf file.

How to move???

Please suggest me guys . .



Regard's,
Thrilok.
Posted

1 solution

As you said, you need to move all the data into another file before deleting one. However, in SQL Server you don't actually play with files but filegroups. When an object is created you define the filegroup where the data goes, not the file. SQL Server makes the decision what file to use if there are multiple files within the file group.

Having that said, the easiest way to move data between filegroups is to create or re-create the indexes for the objects that reside in the filegroup you want to make empty. It's obvious that re-creating the index in another filegroup moves that index but the trick is that re-creating a clustered index actually moves the table data.

This is documented as how-to do in Move an Existing Index to a Different Filegroup[^]

Hope it helps.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900