Error – System.Data.Sqltypes.SqlClent.SqlError:FILESTREAM feature is disable. (Microsoft.SqlServer.Smo)
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.
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.
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.
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
1 2 |
EXEC sp_configure filestream_access_level, 2 RECONFIGURE |
Source: microsoft.com
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
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.
1 2 3 4 |
USE master Go EXEC sp_configure 'show advanced options' GO |
Output
1 2 |
EXEC sp_configure filestream_access_level, 0 GO |
Output
1 2 |
RECONFIGURE WITH OVERRIDE GO |
Output
After enabling the FILESTREAM feature of SQL Server we successfully restored the database on the target server.