How can I change filegroup location?

Question

I used filetable for storing files in my project. now I want to change the filegroup location and move it to another drive. How can I do this?

Answer 1

There is two maners.

1. First dettach the database, move the files, and then reattach the db
2. Second create a new file in the filegroup, and use the command DBCC SHRINKFILE (...) with the EMPTY option, then drop the empty file

FIRST :

EXEC sp_detach_db 'MyDatabase';
--> move the file with a system command
CREATE DATABASE MyDatabase 
   ON FILE (FILENAME = '...',
            FILENAME = '...',
            ...
           )
FOR ATTACH; 

SECOND

ALTER DATABASE MyDatabase 
   ADD FILE (NAME = '...',
             FILENAME = '...',
             SIZE = ... GB,
             FILEGROWTH = 64 MB)
   TO FILEGROUP ...; --> the same filegroupe
DBCC SHRINKFILE ( '...', EMPTYFILE);
ALTER DATABASE MyDatabase 
   REMOVE FILE '...';

First one needs to set the database offline, second does not, but will block all accesses to tables and indexes inside the moved file.