SQL Server – Problem Restoring Database with Filestream

filestreamrestoresql serversql-server-2016

When I restored a database backup file, which uses FILESTREAM feature, everything was fine but after a while the database went to the (recovery pending) mode. I tried restarting SQL Server service and it still didn't work. I decided to delete the database and restore it again but I got this error while restoring:

Restore of database 'Ka4' failed.
(Microsoft.SqlServer.Management.RelationalEngineTasks)

System.Data.SqlClient.SqlError: FILESTREAM feature is disabled.
(Microsoft.SqlServer.SmoExtended)

I've reinstalled SQL Server instance but nothing has changed and it still keeps saying the same error.

According to this page I'm sure that FILESTREAM feature is enabled.

P.S. This is my SQL Server version:

Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64) Apr 29 2016 23:23:58

Best Answer

Thanks to @Nic. It seems that there's a bug with Windows 10 Creators Update. After reading this article I could manage to solve the problem by changing the service startup account to built-in account LocalSystem and now it works.

Related Solutions

Sql-server – Error on SQL Server 2000 backup (.bak file) restored on SQL Server 2012

The database is a SQL Server 2000 database (version 8.00.2055) - and you cannot directly update a SQL Server 2000 database to SQL Server 2012.

You'll need to take an interim step first (restore it in SQL Server 2008, back it up from that server again).

See the relevant MSDN documentation:

In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version.

but SQL Server 2000 is NOT supported.

Sql-server – Restore failed for server (Microsoft.SqlServer.SmoExtended)

Try running RESTORE VERIFYONLY and see if you get more information about the failure.

You could also try running RESTORE with CONTINUE_AFTER_ERROR and then run DBCC CHECKDB:

RESTORE DATABASE database_name 
FROM backup_device WITH CONTINUE_AFTER_ERROR

(i.e. running as TSQL rather than through SMO)

DBCC CHECKDB(N'databasename') WITH EXTENDED_LOGICAL_CHECKS;

Specifying WITH CONTINUE_AFTER_ERROR in a RESTORE statement attempts to restore the database. However, there are many kinds of corruption that prevent recovering a database. We strongly recommend that you reserve using the CONTINUE_AFTER_ERROR option until you have exhausted all alternatives.

All backup strategies should include regular DBCC CHECKDB runs.

Also NOTE: You do not have a backup unless you periodically successfully test restores.

Responding to SQL Server Restore Errors Caused by Damaged Backups

Best Answer

Related Solutions

Sql-server – Error on SQL Server 2000 backup (.bak file) restored on SQL Server 2012

Sql-server – Restore failed for server (Microsoft.SqlServer.SmoExtended)

Related Question