Introduction

Error – System.Data.Sqltypes.SqlClent.SqlError:FILESTREAM feature is disable. (Microsoft.SqlServer.Smo)

Scenario

A database backup from a test server provided to DBA team and asked to restore it on a pre-production SQL Server. Backup was from a SQL Server 2008 version & the target server is also of the same version. A fresh newly created database was present on the target server. Source server database backup needs to restore on the target server database.

Restoration

As usual, we followed the normal restoration process and faced the above-mentioned error notification. We checked and found that the in target SQL Server FILESTREAM feature is not enabled.

Enabling FILESTREAM feature

FILESTREAM is a great feature of SQL Server. It alleviates some of the problems of storing large
unstructured data files such as documents, PDFs, and images, (BLOBs) in a structured database. It is very
easy to enable FILESTREAM on a database/table when you are creating them. Most of us, however, get
to inherit these things and are then told to “fix it.”

We can enable the FILESTREAM feature in ways. In this post, we will discuss all possible ways to do the same.

1. During the installation of SQL Server, we can Enable FILESTREAM Feature
2. Using SQL Server Configuration Manager we can Enable FILESTREAM Feature
3. Using SQL Server Management Studio (SSMS) we can Enable FILESTREAM Feature at SQL Server Instance Level
4. Using Transact SQL (TSQL) we can Enable FILESTREAM Feature

During the installation

During the installation of SQL Server, we can configure FILESTREAM. We do not recommend to enable it during the installation because we can enable it as per our requirements.

By default, it will unchecked

To enable the FILESTREAM feature during installatine we need to check the box

Using SQL Server Configuration Manager

• Open SQL Server Configuration Manager.
• In the list of services, right-click SQL Server Services, and then click Open.

• In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
• Right-click the instance, and then click Properties.

• In the SQL Server Properties dialog box, click the FILESTREAM tab.
• Select the Enable FILESTREAM for Transact-SQL access check box.

• If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
• If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
• Click Apply.
• In SQL Server Management Studio, click New Query to display the Query Editor.
• In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2  
RECONFIGURE

• Click Execute.
• Result

• Restart the SQL Server service.

Source: microsoft.com

Using SQL Server Management Studio (SSMS)

Open SSMS > Connect to server > Right-click on the server instance and go to properties > Advanced. By default FILESTRAEM > FILESTRAEM Access Level will be in Disabled status. We need to enable it.

After enabling > Click OK

Using Transact SQL (TSQL)

The FILESTREAM feature of SQL Server can be enabled using the Transact SQL (TSQL). Open a new query window and type the below TSQL Query to enable FILESTREAM feature.

USE master
Go
EXEC sp_configure 'show advanced options'
GO

Output

EXEC sp_configure filestream_access_level, 0
GO

Output

RECONFIGURE WITH OVERRIDE
GO

Output

Solution for restoration

After enabling the FILESTREAM feature of SQL Server we successfully restored the database on the target server.