Fixing "FILESTREAM feature is disabled" when restoring a SQL Server database

This article documents the troubleshooting steps used to resolve a SQL Server restore failure where the database contained FILESTREAM/FileTable data and SQL Server Management Studio reported:

Restore of database failed.

Microsoft.Data.SqlClient.SqlError:
FILESTREAM feature is disabled.

The issue was eventually resolved by correctly enabling FILESTREAM at both the Windows/instance level and inside SQL Server itself. The investigation also uncovered an important limitation in the original SQL Server 2012 environment.

1. Original environment and first failure

The original database was hosted on SQL Server 2012 and used FILESTREAM/FileTable functionality. A restore was attempted over a pre-production copy of the database, but SQL Server refused to restore it because FILESTREAM was disabled.

The first checks showed that the SQL Server 2012 instance reported:

FilestreamConfiguredLevel = 0
FilestreamEffectiveLevel  = 0

The SQL Server version check also revealed something significant:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (Intel X86)
Enterprise Edition
Windows x64
WOW64

Key finding: the SQL Server engine itself was 32-bit, running under WOW64 on 64-bit Windows.

That explained why FILESTREAM could not be enabled successfully on the SQL Server 2012 instance. SQL Server Configuration Manager returned the error:

There was an unknown error applying the FILESTREAM settings.
Check the parameters are valid. (0x80041008)

2. Configuration Manager checks on SQL Server 2012

The FILESTREAM tab in SQL Server Configuration Manager was checked. The following options were tested:

• Enable FILESTREAM for Transact-SQL access
• Enable FILESTREAM for file I/O access

Initially, file I/O access was enabled without a Windows share name, so that was investigated as a possible cause. However, even with only Transact-SQL access selected, Configuration Manager still failed with 0x80041008.

The correct SQL Server 2012 Configuration Manager snap-in was also tested directly:

C:\Windows\SysWOW64\SQLServerManager11.msc

That produced the same error, confirming that the problem was not simply the wrong Configuration Manager version.

3. Moving the restore to SQL Server 2019

Because SQL Server 2019 supports restoring SQL Server 2012 databases and supports FILESTREAM/FileTable, a new SQL Server 2019 Developer Edition instance was used instead.

Developer Edition is suitable for development and testing. It includes the same database engine features needed for FILESTREAM/FileTable, although it is not licensed for production workloads.

FILESTREAM was then enabled in SQL Server Configuration Manager on the SQL Server 2019 instance.

The server reported:

FilestreamConfiguredLevel = 2
FilestreamEffectiveLevel  = 0
FilestreamShareName       = MSSQLSERVER

This was an important clue. FILESTREAM had been configured in Windows/Configuration Manager, but SQL Server was still not actually using it.

4. Why the share name was not the issue

The FILESTREAM Windows share name does not have to match the database FILESTREAM/FileTable directory name. These are separate settings.

So a share name of MSSQLSERVER was perfectly valid even though the database used a different directory name.

5. The missing SQL Server configuration

The final problem was that FILESTREAM had only been enabled in Configuration Manager. SQL Server also has its own instance-level configuration setting called:

filestream access level

That setting was still effectively disabled.

The current value was checked with:

EXEC sp_configure 'filestream access level';

The fix was to set the access level to 2:

EXEC sp_configure 'filestream access level', 2;
RECONFIGURE;

FILESTREAM access levels are:

6. Verifying the final configuration

After applying the SQL Server configuration, the following query was used to confirm the state:

SELECT
    SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
    SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel,
    SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName;

The desired result was:

FilestreamConfiguredLevel = 2
FilestreamEffectiveLevel  = 2
FilestreamShareName       = MSSQLSERVER

Result: once the effective level was 2, the database restore succeeded.

7. Final working procedure

For a SQL Server instance that needs to restore or host a database using FILESTREAM/FileTable, the working sequence is:

1. Use a supported 64-bit SQL Server instance.
2. Open SQL Server Configuration Manager.
3. Open the SQL Server service properties.
4. Open the FILESTREAM tab.
5. Enable FILESTREAM for Transact-SQL access.
6. Enable FILESTREAM for file I/O access if FileTable or Win32 streaming access is required.
7. Specify a Windows share name if file I/O access is enabled.
8. Restart the SQL Server service if required.
9. Run:

EXEC sp_configure 'filestream access level', 2;
RECONFIGURE;

Then verify:

SELECT
    SERVERPROPERTY('FilestreamConfiguredLevel'),
    SERVERPROPERTY('FilestreamEffectiveLevel'),
    SERVERPROPERTY('FilestreamShareName');

Both configured and effective levels should normally be 2 when FILESTREAM file I/O access is required.

8. Main lessons learned

• Enabling FILESTREAM in SQL Server Configuration Manager alone is not enough.
• sp_configure 'filestream access level' must also be set appropriately.
• FilestreamConfiguredLevel and FilestreamEffectiveLevel are useful for diagnosing partial configuration.
• The Windows FILESTREAM share name does not need to match the database FILESTREAM/FileTable directory name.
• SQL Server Developer Edition supports FILESTREAM and FileTable for development/test use.
• A 32-bit SQL Server installation on 64-bit Windows can prevent FILESTREAM from being enabled and may surface as the misleading 0x80041008 error.

Documented from a real SQL Server 2012 to SQL Server 2019 restore troubleshooting exercise involving a database using FILESTREAM/FileTable.