Rajendra Gupta

Working with SQL Server FILESTREAM – Adding columns and moving databases

January 23, 2019 by

SQL Server FILESTREAM is a great feature to store unstructured data into the file system with the metadata into SQL Server database. In the article, FILESTREAM in SQL Server, we wrote to enable the FILESTREAM feature at the instance level. We created the new database for the FILESTREAM and insert sample data into it in the Managing data with SQL Server FILESTREAM tables.

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.

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.

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.

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)

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.

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.

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

  1. Detach and attach method
  2. 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.

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.

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.

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.

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.

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.

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

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS
SQL FILESTREAM and SQL Server Full Text search
SQL Server FILESTREAM and Replication
SQL Server FILESTREAM with Change Data Capture
Transaction log backups in a SQL FILESTREAM database
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping
SQL Server FILETABLE – the next generation of SQL FILESTREAM
Managing Data in SQL Server FILETABLEs
SQL Server FILETABLE Use Cases

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

990 Views