SQL Server FILESTREAM is a great feature to store unstructured data into the file system with the metadata into
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 feature is enabled at the instance level, you can verify it from the SQL Service properties in the SQL Server Configuration Manager
- You have configured the filestream_access_level at the instance level using the sp_configure command
Worked example
Let us create a new database and create a sample table into it.
|
1 2 3 4 5 6 7 8 9 |
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.
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 2 |
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.
|
1 2 |
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)
|
1 |
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.
|
1 2 3 |
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.
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.
|
1 |
ALTER TABLE [dbo].[SQLShack] ADD [BLOBData] [varbinary](max) FILESTREAM NULL |
We can verify the FILESTREAM column into the existing table as shown below.
Insert the data into the table to verify it has no issues.
You can see the object in the FILESTREAM container as well.
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
- Detach and attach method
- Alter database command to move the database files
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.
|
1 2 3 4 5 6 7 8 |
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.
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.
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.
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.
|
1 2 3 4 |
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
Attach the FILESTREAM database from the new location: To attach the database, right click on the databases node and click ‘Attach’
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.
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.
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.
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.
Let us detach the database again and rename the FILESTREAM container name to ‘DemoSQL_Old’ at the file system level.
If we try to attach the FILESTREAM database again with SSMS, you get the below error.
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.
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.
|
1 2 3 4 5 6 7 8 |
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 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.
Alternatively, run the below command to take database offline.
|
1 2 3 4 |
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.
Execute the below query for each database file including the FILESTREAM container also.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 3 4 |
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.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023