SQL Server FILESTREAM is a great feature to store unstructured data into the file system with the metadata into
SQL Server FILESTREAM是一项很棒的功能，可以将非结构化数据与元数据一起存储到文件系统中
Sometimes we might have a requirement to add the FILESTREAM data to an existing table. In this article, we will learn to fulfil this requirement. Before we move further, we will assume that the following tasks are already completed.
有时我们可能需要将FILESTREAM数据添加到现有表中。 在本文中，我们将学习满足此要求。 在继续之前，我们将假定以下任务已经完成。
- FILESTREAM feature is enabled at the instance level, you can verify it from the SQL Service properties in the SQL Server Configuration Manager 在实例级别启用了FILESTREAM功能，您可以从SQL Server配置管理器中SQL服务属性中进行验证
- You have configured the filestream_access_level at the instance level using the sp_configure command 您已使用sp_configure命令在实例级别配置了filestream_access_level。
Worked example
工作的例子
Let us create a new database and create a sample table into it.
让我们创建一个新的数据库，并在其中创建一个示例表。
Create Database DemoSQL
go
use DemoSQL
Go
CREATE TABLE [dbo].[SQLShack]
(
[EmpID] [int] NOT NULL,
[EmpName] varchar(20) Not NULL
)
Below are the steps to add the FILESTREAM column into existing table.
下面是将FILESTREAM列添加到现有表中的步骤。
Step 1: Add FILESTEAM filegroup: We need to add the FILESTEAM file group into existing database and specify that it will contain the FILESTREAM objects. Run the below query to add FILESTREAM filegroup.
步骤1：添加FILESTEAM文件组：我们需要将FILESTEAM文件组添加到现有数据库中，并指定它将包含FILESTREAM对象。 运行以下查询以添加FILESTREAM文件组。
ALTER DATABASE DemoSQL ADD FILEGROUP FILESTREAM_grp CONTAINS FILESTREAM
GO
Step 2: Add the file into the FILESTEAM filegroup: In this step, we are going to add a database file into the FILESTREAM filegroup. Execute the below query in the sample database.
步骤2：将文件添加到FILESTEAM文件组中：在这一步中，我们将添加数据库文件到FILESTREAM文件组中。 在示例数据库中执行以下查询。
ALTER DATABASE DemoSQL ADD FILE ( NAME = N'DemoSQLFiles', FILENAME = N'C:\sqlshack\DemoSQL\FS' ) TO FILEGROUP FILESTREAM_grp
GO
Step 4: ADD FILESTREAM Column to an existing table: In this step, we can add the column into an existing table using the alter table command. (Note: To show the below error message, I added the step 4 before)
步骤4：将FILESTREAM列添加到现有表中：在这一步中，我们可以使用alter table命令将列添加到现有表中。 （注意：要显示以下错误消息，我在前面添加了步骤4）
ALTER TABLE [dbo].[SQLShack] ADD [BLOBData] [varbinary](max) FILESTREAM NULL
We get the below error message with this query.
通过此查询，我们得到以下错误消息。
Step 3: ADD non-null unique column with ROWGUIDCOL property: We need to add the new column into the existing table with ROWGUIDCOL property. We can execute the below command to alter the table for this.
步骤3：使用ROWGUIDCOL属性添加非空唯一列：我们需要使用ROWGUIDCOL属性将新列添加到现有表中。 我们可以执行以下命令来更改此表。
Alter table [dbo].[SQLShack]
Add FSUnique uniqueidentifier not null ROWGUIDCOL unique default newid()
GO
We can verify the table property using the sp_help ‘tablename’ command. It creates the unique non-clustered index along with the unique, default constraint on this new column. It ensures the unique values in the table. It also ensures the performance benefit as well due to the non-clustered index.
我们可以使用sp_help'tablename'命令验证表属性。 它在此新列上创建唯一的非聚集索引以及唯一的默认约束。 它确保表中的唯一值。 由于非聚集索引，它还确保了性能优势。
Now let us go back to step 4 as mentioned above and run the query to add SQL Server FILESTREAM column into the existing table.
现在，让我们回到上面提到的步骤4，并运行查询以将SQL Server FILESTREAM列添加到现有表中。
ALTER TABLE [dbo].[SQLShack] ADD [BLOBData] [varbinary](max) FILESTREAM NULL
We can verify the FILESTREAM column into the existing table as shown below.
我们可以将FILESTREAM列验证到现有表中，如下所示。
Insert the data into the table to verify it has no issues.
将数据插入表中以验证它没有问题。
You can see the object in the FILESTREAM container as well.
您也可以在FILESTREAM容器中看到该对象。
将SQL Server FILESTREAM数据库移动到另一个位置 (Moving a SQL Server FILESTREAM database to another location)
We might need to move the FILESTREAM database to another location. It might be due to the space-related issues or any requirement from the storage side. Suppose we need to move the FILESTREAM database so normally DBA follow the below approaches
我们可能需要将FILESTREAM数据库移动到另一个位置。 这可能是由于与空间有关的问题或存储方面的任何要求。 假设我们需要移动FILESTREAM数据库，因此通常DBA遵循以下方法
- Detach and attach method 拆装方法
- Alter database command to move the database files Alter Database命令移动数据库文件
We should be having the database files details before we plan to move the database from one location to another. We can get the list of all database files using the sys.database_files. We should run this under the database context.
在计划将数据库从一个位置移动到另一位置之前，我们应该具有数据库文件的详细信息。 我们可以使用sys.database_files获得所有数据库文件的列表。 我们应该在数据库上下文中运行它。
Use DemoSQL
Go
SELECT
name AS Name,
physical_name AS PhysicalName,
state_desc AS State
FROM sys.database_files
GO
As shown above, we have the Primary database file (.MDF), the transaction log file (.ldf) and the FILESTREAM database file.
如上所示，我们具有主数据库文件（.MDF），事务日志文件（.ldf）和FILESTREAM数据库文件。
拆装方法 (Detach and Attach Method)
Let us move the SQL Server FILESTREAM database using the Detach and Attach method first. Follow the below steps to move this FILESTREAM database using this approach.
让我们首先使用“分离和附加”方法移动SQL Server FILESTREAM数据库。 请按照以下步骤使用此方法移动此FILESTREAM数据库。
Detach the database: Right click on the database and then follow Tasks -> Detach
分离数据库：右键单击数据库，然后按照任务->分离
We should not have any active connection for the database to detach it.
我们不应该有任何活动的连接来使数据库分离。
We can get more information from the hyperlink in the message tab. Below information is displayed once we click on the hyperlink.
我们可以从“消息”选项卡中的超链接获取更多信息。 单击超链接后，将显示以下信息。
We can close all the active connections by using the KILL command or from the Activity Monitor in SSMS. Once we have closed all active connections, its status becomes as ‘Ready’ in the detach database wizard.
我们可以使用KILL命令或从SSMS中的“活动监视器”关闭所有活动的连接。 一旦我们关闭了所有活动的连接，它的状态在分离数据库向导中将变为“就绪”。
Click ‘Ok’ to detach the database. We can also use the below query to detach FILESTREAM database. Once database is detached, it will not show the database in the database list of SSMS.
单击“确定”以分离数据库。 我们还可以使用以下查询来分离FILESTREAM数据库。 分离数据库后，它将不会在SSMS的数据库列表中显示该数据库。
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DemoSQL'
GO
Copy the database files into a new folder: In this example, we want to move the database files to the location ‘C:\MoveDB’. We have copied all the files into the new location as shown below
将数据库文件复制到一个新文件夹中：在本示例中，我们要将数据库文件移动到位置“ C：\ MoveDB”。 我们已将所有文件复制到新位置，如下所示
Attach the FILESTREAM database from the new location: To attach the database, right click on the databases node and click ‘Attach’
从新位置附加FILESTREAM数据库：要附加数据库，请右键单击数据库节点，然后单击“附加”
Click on ‘Add’ and provide the MDF files from the new path. It the below section, you can notice that it identifies the corresponding log file. It does not show any SQL Server FILESTREAM file in this wizard.
单击“添加”，然后从新路径提供MDF文件。 在下面的部分中，您可以注意到它标识了相应的日志文件。 它在此向导中不显示任何SQL Server FILESTREAM文件。
Let us try to attach this database. We get the below error if the SQL Server is not able to access the files due to permissions.
让我们尝试附加该数据库。 如果SQL Server由于权限而无法访问文件，则会出现以下错误。
We can provide the permission for the SQL Service account for the new folder and then try again the attach database process. It works fine this time. Let us view the location of database files again.
我们可以为新文件夹SQL Service帐户提供权限，然后重试附加数据库过程。 这次工作正常。 让我们再次查看数据库文件的位置。
In the above screenshot, we can see.MDF and.LDF files are pointing to a new location but FILESTREAM still pointing to the old location. We want the FILESTREAM to point to the new location as well.
在上面的屏幕截图中，我们可以看到.MDF和.LDF文件指向一个新位置，但是FILESTREAM仍然指向旧位置。 我们也希望FILESTREAM也指向新位置。
Let us detach the database again and rename the FILESTREAM container name to ‘DemoSQL_Old’ at the file system level.
让我们再次分离数据库，然后在文件系统级别将FILESTREAM容器名称重命名为'DemoSQL_Old'。
If we try to attach the FILESTREAM database again with SSMS, you get the below error.
如果我们尝试再次使用SSMS附加FILESTREAM数据库，则会出现以下错误。
It is not able to locate the SQL Server FILESTREAM folder because we have renamed it. We did not get any option in SSMS to modify the FILESTREAM folder location. We need to do it using T-SQL only.
由于我们已将其重命名，因此无法找到SQL Server FILESTREAM文件夹。 SSMS中没有任何选项可以修改FILESTREAM文件夹的位置。 我们只需要使用T-SQL即可。
In the command, we can specify the location of the .MDF, .LDF and the FILESTREAM container. SQL Service account should be having the access on all these files and container. Execute the below command to attach the FILESTREAM database. You can get the file and container name from the sys.databases output we shown earlier.
在命令中，我们可以指定.MDF，.LDF和FILESTREAM容器的位置。 SQL Service帐户应具有对所有这些文件和容器的访问权限。 执行以下命令来附加FILESTREAM数据库。 您可以从前面显示的sys.databases输出中获取文件和容器名称。
USE [master]
GO
CREATE DATABASE [DemoSQL] ON
( FILENAME = N'C:\MoveDB\DemoSQL.mdf' ),
( FILENAME = N'C:\MoveDB\DemoSQL_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'DemoSQLFiles', FILENAME = N'C:\MoveDB\DemoSQL\FS' )
FOR ATTACH
Once database is attached, let us verify the database files path again. You can see that all the files are now pointing to the new location in which we decided to move the database files.
附加数据库后，让我们再次验证数据库文件路径。 您会看到所有文件现在都指向我们决定将数据库文件移动到的新位置。
Alter Database命令移动数据库文件 (Alter database command to move the database files )
We can also use the alter database commands to move the SQL Server FILESTREAM database file. Right click on the database and take it offline. You should not have any active connections to take database in the offline state.
我们还可以使用alter database命令移动SQL Server FILESTREAM数据库文件。 右键单击数据库，然后使其脱机。 您不应具有任何活动连接以使数据库处于脱机状态。
Alternatively, run the below command to take database offline.
或者，运行以下命令使数据库脱机。
USE [master]
GO
ALTER DATABASE [DemoSQL] SET OFFLINE
GO
Now I renamed my folder from ‘C:\MoveDB’ to ‘C:\MoveDB_Alter’ . We need to make changes in the system catalog to reflect this change. We cannot database in the online state if the system catalog does not reflect this change.
现在，我将文件夹从'C：\ MoveDB'重命名为'C：\ MoveDB_Alter'。 我们需要在系统目录中进行更改以反映此更改。 如果系统目录未反映此更改，那么我们将无法在线存储数据库。
Execute the below query for each database file including the FILESTREAM container also.
对每个数据库文件（包括FILESTREAM容器）执行以下查询。
ALTER DATABASE [DemoSQL]
MODIFY FILE ( NAME = DemoSQL,
FILENAME = 'C:\MoveDB_Alter\DemoSQL.mdf');
GO
ALTER DATABASE [DemoSQL]
MODIFY FILE ( NAME = DemoSQL_log,
FILENAME = 'C:\MoveDB_Alter\DemoSQL_log.ldf');
GO
ALTER DATABASE [DemoSQL]
MODIFY FILE ( NAME = DemoSQLFiles,
FILENAME = 'C:\MoveDB_Alter\DemoSQL\FS');
GO
You get the confirmation message that a new path will be used the next time database is restarted.
您收到确认消息，下次数据库重新启动时将使用新路径。
Now, take the database in Online state from the ‘Bring Online’ tab.
现在，从“联机”选项卡中使数据库处于联机状态。
Alternatively, run the below command.
或者，运行以下命令。
USE [master]
GO
ALTER DATABASE [DemoSQL] SET ONLINE
GO
Once the database is online, verify the database file paths.
数据库联机后，请验证数据库文件路径。
结论： (Conclusion:)
In this article, we explored how to add a SQL Server FILESTREAM column into an existing table. We also moved the FILESTREAM database to another location. We will cover more topics regarding the FILESTREAM feature in future articles.
在本文中，我们探讨了如何将SQL Server FILESTREAM列添加到现有表中。 我们还将FILESTREAM数据库移动到了另一个位置。 在以后的文章中，我们将讨论有关FILESTREAM功能的更多主题。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/working-with-sql-server-filestream-adding-columns-and-moving-databases/
215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
