sql-serverfilestreamfiletablefilegroup

How can I change filegroup location?


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?


Solution

  • 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.