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?
There is two maners.
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.