How to Remove Filestream from a SQL Server Database

By:   |   Comments (2)   |   Related: > FILESTREAM


Problem

We are hosting a SQL Server database that has the FILESTREAM feature turned on. A request came in to restore it to another SQL Server which does not have the FILESTREAM feature enabled. The request for this work states that it is for testing purposes and the end user doesn't care about the FILESTREAM data stored in the database. When I tried to restore it on the target server, I got an error that FILESTREAM is not enabled and the restore failed. How I can accomplish this task?

Solution

On the surface it looks like a simple problem, but in actuality we have to jump through several hoops to get what we want. It is not too complex, but it is nice to have a step by step process to smoothly complete this task. Simply deleting the links to the files that are stored in this FILESTREAM table will not enable the database to be restored on the target server. There are extra steps we need to take to remove all traces of FILESTREAM from this database.

The problem we are trying to resolve is that we have a FILESTREAM enabled database that needs to be restored on a non-FILESTREAM enabled SQL Server and we need to remove the FILESTREAM data and FILESTREAM feature from this database in order to be able to restore it. For many reasons we cannot and do not want to do it on the source system. Maybe it is our production database and we do not want to change any database settings there.  One way to solve this problem is to restore this database to another test system where FILESTREAM is enabled and then remove the features.

Background of the SQL Server FILESTREAM feature

Before we dive into the process itself, let's understand a few basic facts about FILESTREAM.

FILESTREAM allows BLOB data to be stored on the NTFS based disk subsystem instead of storing them in a database. This feature was first introduced in SQL Server 2008. Some examples of BLOB data are images, videos, PDF files, etc. The advantage of storing these files on disk instead of in the database is that they are accessed faster and do not create locking for other transactions that are happening in the same database.

Only links to the BLOB data are stored in the database, for which we have to create a separate file and a filegroup at the time of database creation or add later with ALTER statements. These objects are marked to be used by FILESTREAM. In SQL Server 2012, an enhancement to the FILESTREAM was introduced with a special type of table called FileTable. This table type allows for better organization of FILESTREAM data on a FILESTREAM enabled SQL Server. For the purpose of this article we are going to stick with an example of a normal table with a FILESTREAM column.

Enable FILESTREAM for the SQL Server Instance

Here is a great tip describing how to enable the FILESTREAM feature on a SQL Server level.

To verify if FILESTREAM is enabled on an instance, I ran the following query on my instance and here is the result.

SELECT SERVERPROPERTY('FileStreamShareName') as Share_Name,   
SERVERPROPERTY('FIleStreamConfiguredLevel') as Config_Level,  
SERVERPROPERTY('FileStreamEffectiveLevel') as Effective_Level
filestream feature enabled

FILESTREAM has various access levels which are defined in the chart below. You can read about the access levels in detail at this link.

Value Definition
0 Disables FILESTREAM support for this instance.
1 Enables FILESTREAM for Transact-SQL access.
2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.

Create Database and Enable FILESTREAM for SQL Server Database

Let's go through the process of creating a database containing FILESTREAM filegroup and File.

  • First create the database, this does not require any special syntax.
  • Next add a new filegroup to the database with the clause FILESTREAM in the code.
  • Then allocate a file to this filegroup. You have to give it a logical name (stores in database Meta data) and physical file name (that will reside on the disk subsystem).
  • Set FILESTREAM directory and enable it at the database level
-- Create database
IF EXISTS (  SELECT * FROM sys.databases  WHERE name = N'MyFS_Db')
DROP DATABASE MyFS_Db
GO

CREATE DATABASE MyFS_Db;
GO

-- Add FILESTREAM file and filegroup
ALTER DATABASE MyFS_Db ADD FILEGROUP MyFS_Db_filestream CONTAINS FILESTREAM
GO

ALTER DATABASE MyFS_Db ADD FILE
(
    NAME= 'MyFS_Db_filestream',
    FILENAME = 'C:\MSSQL\Data\MyFS'
) TO FILEGROUP MyFS_Db_filestream;
GO

-- Set FILESTREAM directory and enable it at a database level.
USE MyFS_Db;
GO
 
ALTER DATABASE MyFS_Db  
SET FILESTREAM( DIRECTORY_NAME = 'MyFS_Db_fs' ) WITH NO_WAIT;
GO

ALTER DATABASE MyFS_Db 
SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL ) WITH NO_WAIT;
GO

Below we can check database MyFS_Db properties for the FILESTREAM settings.

filestream feature for database

 Now let's create a table MyFSTable to hold a FILSTREAM column called FileStreamFile of data type varbinary(MAX).

USE MyFS_Db;
GO

-- Create Custom FILESTREAM table
CREATE TABLE MyFSTable 
   (ID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE NOT NULL, 
    PathToDirectory VARCHAR(MAX), 
    FileName VARCHAR(MAX), 
    CreateDate DATETIME, 
    FileSize NUMERIC(8,4),
    FileStreamFile VARBINARY(MAX) FILESTREAM);

The following diagram shows the table structure in SQL Server Management Studio (SSMS).

table with filestream column

If we script out MyFSTable from SSMS by right clicking on it, this is how the definition looks which is quite different from the definition we used to create this table. This is because SQL Server is optimized for FILESTREAM when this feature is enabled and creates objects to support it.

USE [MyFS_Db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyFSTable](
   [ID] [UNIQUEIDENTIFIER] ROWGUIDCOL  NOT NULL,
   [PathToDirectory] [VARCHAR](MAX) NULL,
   [FileName] [VARCHAR](MAX) NULL,
   [CreateDate] [DATETIME] NULL,
   [FileSize] [NUMERIC](8, 4) NULL,
   [FileStreamFile] [VARBINARY](MAX) FILESTREAM  NULL,
UNIQUE NONCLUSTERED 
([ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [MyFS_Db_filestream]
GO

I have exported data from the AdventureWorks2017.Production.ProductInventory table into an Excel spreadsheet on my local drive and named the file C:\MSSQL\AW2017_Product_Inventory.xlsx.

I will now insert this Excel file as BLOB in table MyFSTable.

-- Load the document in the table
DECLARE @File AS VARBINARY(MAX)
 
SELECT @File = CAST(BulkColumn AS VARBINARY(MAX))
         FROM OPENROWSET(BULK 'C:\MSSQL\AW2017_Product_Inventory.xlsx', SINGLE_BLOB ) AS fs_FILE;
 
INSERT INTO dbo.MyFSTable
( ID,PathtoDirectory,FileName,CreateDate,FileSize,FileStreamFile )
 
SELECT NEWID(), 'C:\MSSQL', 'AW2017_Product_Inventory', GETDATE(), 10 , @File 

Select from the table to verify.

SELECT * FROM dbo.MyFSTable
list of rows

Backup and Restore Database on SQL Server that does not have FILESTREAM enabled

First create the backup using this script or use the SSMS GUI to create the backup.

BACKUP DATABASE [MyFS_Db] TO DISK = N'MyFS_Db.bak' 

After we do the backup we can issue the following command to see the files that are part of the backup file.  This will show the data file, log file and the filestream file.

RESTORE FILELISTONLY FROM DISK = 'MyFS_Db.bak'

If we try to restore the database on a server that does not have FILESTREAM enabled using the SSMS GUI, we get this error message "FILESTREAM feature is disabled".

restore error for filestream

Remove Filestream from SQL Server Database

To solve this problem, we will restore the MyFS_Db database backup on a server where FILESTREAM is enabled and then remove the FILESTREAM features from the database. Read this tip to learn how to enable FILESTREAM for the instance.

Once this database is successfully restored, we need to remove the FILESTREAM feature from the database.  There are couple of ways to remove FILESTREAM successfully from the database.

Solution 1: Remove the FILESTREAM column then remove the FILESTREAM features

In this case, we simply drop the FILESTREAM column from the table and leave the other data in MyFSTable intact.  Note if you had multiple tables with FILESTREAM enabled you would need to do this for all of the tables.

In the code below,

  • We ALTER the table to remove FILESTREAM for the table,
  • Then we do a ShrinkFile to empty the FILESTREAM filegroup
  • Then we use the filestream garbage collection stored procedure to cleanup any left over data.  Note that this is done mutiple times and in between each of these command we issue a checkpoint.  Each time it runs the output values will be different for the columns num_collected_items, num_marked_for_collection_items and num_processed_items until all three are 0.  In testing we noticed that you need to run the garbage collection 3 times.
  • Then we remove the file and the filegroup for the database.
USE [MyFS_Db]
GO

-- Keep the data in the table only remove the column
ALTER TABLE dbo.MyFSTable drop column FileStreamFile
GO
ALTER TABLE dbo.MyFSTable SET (FILESTREAM_ON="NULL") 
GO
 
-- Cleanup
DBCC SHRINKFILE('MyFS_Db_filestream', EMPTYFILE)
GO
EXEC sp_filestream_force_garbage_collection @dbname = 'MyFS_Db', @filename = 'MyFS_Db_filestream'
GO
checkpoint
GO
EXEC sp_filestream_force_garbage_collection @dbname = 'MyFS_Db', @filename = 'MyFS_Db_filestream'
GO
checkpoint
GO
EXEC sp_filestream_force_garbage_collection @dbname = 'MyFS_Db', @filename = 'MyFS_Db_filestream'
GO
checkpoint
GO
 
-- Remove FILESTREAM file and filegroup
ALTER Database [MyFS_Db] REMOVE FILE MyFS_Db_filestream
GO
ALTER Database [MyFS_Db] REMOVE FILEGROUP [MyFS_Db_filestream]
GO 

After the above is done, the FILESTREAM feature has been removed from the database and you can backup the database and restore to a server that does not have FILESTREAM enabled.

Solution 2: Remove the FILESTREAM table then remove the FILESTREAM features

If the requestor is not going to use the data in FILESTREAM table in the target system then you can drop the table entirely and follow the same steps we discussed above.  In testing, we noticed that you only need to run the garbage collection process 2 times.

USE [MyFS_Db]
GO
 
-- drop the table if it is not needed
DROP table dbo.MyFSTable
GO
 
-- Cleanup
DBCC SHRINKFILE('MyFS_Db_filestream', EMPTYFILE)
GO
EXEC sp_filestream_force_garbage_collection @dbname = 'MyFS_Db', @filename = 'MyFS_Db_filestream'
GO
checkpoint
go
EXEC sp_filestream_force_garbage_collection @dbname = 'MyFS_Db', @filename = 'MyFS_Db_filestream'
GO
checkpoint
GO
 
-- Remove FILESTREAM file and filegroup
ALTER Database [MyFS_Db] REMOVE FILE MyFS_Db_filestream
GO
ALTER Database [MyFS_Db] REMOVE FILEGROUP [MyFS_Db_filestream]
GO

After the above is done, you can backup the database and restore to a server that does not have FILESTREAM enabled.

Next Steps
  • Enabling the FILESTREAM feature is explained in this tip.
  • FILESTREAM access levels are explained here and here.
  • SQL Server Garbage Collection Process is explained very well here.
  • Refer to other MSSQLTips tips on the SQL Server FILESTREAM feature.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ameena Lalani Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, February 19, 2020 - 2:49:50 PM - Ameena Lalani Back To Top (84643)

Sqldev,

Thanks for your question. Please see this the fix in this link to see if it applies to your situation. https://support.microsoft.com/en-in/help/4469722/internal-errors-when-updating-filestream-tombstone-system-table

Usually "file is not empty" message points to the fact that one of the VLF (virtual log file) still active and will not allow garbage collector to cleanup that last file till that happens. Try adding the garbage collector cleanup script as a second step of your transaction log backup job.


Friday, February 14, 2020 - 12:02:55 PM - sqldev Back To Top (84489)

Hello Ameena, Thanks for writing this great post. I have used your code for Removing FileStream from SQL Server Database(Solution 1). I used to encounter no issues, but recently sometimes I get error:  'The file cannot be removed because it is not empty'. What could be the reason for this error and how can I fix it.















get free sql tips
agree to terms