How to modify filepath of datafile that is inside a filegroup? Is it the same approach on alter database modify file directly the file that is inside a filegroup? Or need to do alter database modify filegroup modify file?

Question

How to modify filepath of datafile that is inside a filegroup? Is it the same approach on alter database modify file directly the file that is inside a filegroup? Or need to do alter database modify filegroup modify file?

Answer 1

To modify the location if a file, use

ALTER DATABASE db MODIFY FILE (NAME = 'logical_name', FILEPATH = 'newfilepath')
ALTER DATABASE db SET OFFLINE

At this point, move the data file to the new location and then run

ALTER DATBASE db SET ONLINE

All data files in a database belong to a file group.

Answer 2

Hi，Michelle Urbano

Welcome to Microsoft Q&A fourm!

Please do not directly move the physical location of the database files, as this could cause metadata location failures.

As Erland mentioned, data files all belong to file groups, so you need to use the command to move them.

ALTER DATABASE [YourDatabaseName] MODIFY FILE (NAME = LogicalFileName, FILENAME = 'C:\NewPath\YourDataFile.ndf') 

However, before moving, please take the database offline, and after moving, verify the files and bring the database online.

For specific operations and details, please refer to the documentation ：Move Database Files

Best Regards,

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.