SQL Server Filetable in different local disk

Question

Can I change filetables share disk?

I've enable filestream on my db with a script like this

DECLARE @dbName             varchar(50),
        @pathFileStream     varchar(max),
        @directoryNameFS    varchar(max), 
        @sql                varchar(max)

SET @dbName = 'mydb'
SET @pathFileStream = 'D:\fs'
SET @directoryNameFS = 'FileTableStream'

SET @sql = '

--0° set file stream full
ALTER DATABASE ' + @dbName + ' SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL ) WITH NO_WAIT

--1° Add FileGroup
ALTER DATABASE ' + @dbName + '
ADD FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
GO

ALTER DATABASE ' + @dbName + '
ADD FILE (NAME = FileStreamData, FILENAME = ''' + @pathFileStream + ''')
TO FILEGROUP FileStreamFileGroup
GO

ALTER DATABASE ' + @dbName + ' SET FILESTREAM( DIRECTORY_NAME = ''' + @directoryNameFS + ''' ) WITH NO_WAIT
GO

'

PRINT @sql

And then I create a filetable:

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'documentStore') 
BEGIN
    CREATE TABLE [dbo].[documentStore] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamFileGroup]
    WITH
    (
        FILETABLE_DIRECTORY = N'documentStore', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS
    )   
END

At this time my 'documentStore' is sharing on the same disk of database (disk C:\). I need to point on the same disk of filestream (D:).

score 0 · Answer 1 · answered Oct 10 '21 at 11:28

At this time my 'documentStore' is sharing on the same disk of database (disk C:). I need to point on the same disk of filestream (D:).

Create a filegroup with a file on the same drive as the file table directory and specify that on the CREATE TABLE statement.

--add filegroup for file table rows
ALTER DATABASE mydb
    ADD FILEGROUP YourFileGroupOnD;
--add file on D drive
ALTER DATABASE mydb
    ADD FILE (NAME = YourFileGroupOnDFile, FILENAME = 'D:\DataFiles\mydb_YourFileGroupOnDFile.ndf')
    TO FILEGROUP YourFileGroupOnD;
GO

--specify the new filegroup for file table rows instead of PRIMARY
CREATE TABLE [dbo].[documentStore] AS FILETABLE ON [YourFileGroupOnD] FILESTREAM_ON [FileStreamFileGroup]
WITH
(
    FILETABLE_DIRECTORY = N'documentStore', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS
);   
GO

SQL Server Filetable in different local disk

1 Answers1