By: Ameena Lalani | 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 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.
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).
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
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".
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.
About the author
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
