SQL Server文件在不同的本地磁盘

Question

我可以更改文件共享磁盘吗?

我已经启用我的数据库上的文件流脚本像这样

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

然后创建一个文件:

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

此时，我的'documentStore'在数据库的同一磁盘(磁盘C:)上共享。我需要指向文件流的同一个磁盘(D:)。

Answer 1

此时，我的'documentStore'在同一磁盘上共享数据库(C盘:)。我需要指向文件流的同一个磁盘(D)。

创建一个与文件表目录在同一驱动器上的文件组，并在CREATE TABLE语句中指定。

--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:DataFilesmydb_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