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?

Michelle Urbano 40 Reputation points
2024-06-19T19:17:17.9066667+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,268 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106K Reputation points MVP
    2024-06-19T21:03:59.8566667+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. MikeyQiaoMSFT-0444 1,750 Reputation points
    2024-06-20T03:20:53.73+00:00

    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.