Configuring a software feature is usually much harder than removing it. Most of the times, removing a feature could be just a matter of a mouse click or a simple command/statement.

So what do you think about this question? Enabling FILESTREAM storage on a database and removing it completely from a database – which one is harder?. Well the correct answer to this question may be ‘It depends!’.

Removing FILESTREAM features completely from a SQL Server 2008 may be a little bit trickier than you would expect. This post explains how to remove the FILESTREAM storage features from a database.

Why would someone ever want to remove the FILESTREAM storage features? Well, this is not something that you will frequently do. One scenario could be while moving the production database to a dev or testing environment where the FILESTREAM data is not relevant any more. In such a case, to reduce the database size, one might decide to remove FIELSTREAM storage completely from the database.

Removing FILESTREAM Storage from a database

At first look, it looks like removing FILESTREAM features from a database will be just a matter of the dropping the FIELSTREAM columns and removing the FILESTREAM filegroup from the database.

Unfortunately, it is a little bit more complicated than that. To completely remove FILESTREAM features from a database, you need to perform the following steps.

1. Delete all FILESTREAM columns from all tables
2. Disassociate tables from FILESTREAM filegroups (we will see this shortly)
3. Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)
4. Remove all FILESTREAM filegroup (there may be more than one of them)

Let us now see how to do this. Before we perform the above operations, we need to have a database with FILESTREAM features. Run the script below to create such a database.

-- Create a FILESTREAM Enabled Database
CREATE DATABASE NorthPole 
ON
PRIMARY ( 
    NAME = NorthPoleDB, 
    FILENAME = 'C:\Data\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS_FG CONTAINS FILESTREAM( 
    NAME = NorthPoleFS_FILE,
    FILENAME = 'C:\FS\NorthPoleFS')
LOG ON ( 
    NAME = NorthPoleLOG,
    FILENAME = 'C:\Data\NorthPoleLOG.ldf')
GO

-- CREATE a TABLE with FILESTREAM columns
USE NorthPole
GO
CREATE TABLE [dbo].[Items](
   [ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [ItemNumber] VARCHAR(20),
   [ItemDescription] VARCHAR(50),
   [ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

Dropping FILESTREAM Columns

So lets get into the destructive mood. Our job is to remove the FILESTREAM storage completely from this database. Let us start with dropping the FILESTREAM column from the Item Table.

ALTER TABLE Items DROP column ItemImage

In a real-world scenario there may be more than one table with FILESTREAM columns and each table may have more than one FILESTREAM columns. You need to drop all of them.

Disassociating tables from FILESTREAM filegroups

This is a tricky step and often confusing. Each table that has one or more FILESTREAM columns is associated to a FILESTREAM filegroup in the database. This association is usually made when the table is created. If your database has more than one FILESTREAM filegroup, you can specify in which filegroup you wish to store the FILESTREAM data of that table.

This association exists even after you drop all the FIELSTREAM columns. To SQL Server, it will still look like the FILESTREAM filegroup file is in use. So before deleting the FILESTREAM filegroup file (FILESTREAM Data Container), you need to remove this association. The following TSQL code does this.

-- Disassociate table "items" from its FILESTREAM file group
ALTER TABLE Items SET (FILESTREAM_ON = "NULL")

Removing FILESTREAM filegroup files

The next step is to remove the FILESTREAM filegroup file. The following TSQL code shows how to do this. If the database has more than one FILESTREAM filegroup, you need to do it for the filegroup file of each of them.

-- Remove the File
ALTER DATABASE NorthPole Remove file NorthPoleFS_FILE;

Removing FIELSTREAM filegroups

And finally, you are ready to drop the FILESTREAM filegroup. The following TSQL code snippet demonstrates it. If the database has more than one FILESTREAM filegroup, the operation needs to be performed for each filegroup.

-- Remove the File Group
ALTER DATABASE NorthPole REMOVE FILEGROUP NorthPoleFS_FG;

If you have got a FILESTREAM question, feel free to post it to this forum.