Enabled filestream from the SQL server Services and restart the SQL Server services for the changes to take effect.
— Enable Filestream, by default its set to 0 (disabled)
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
After configuration change take effect the “FILESTREAM Access Level” will change to “Full access enabled”
Add FileStream FileGroup and file to the Database
USE [master] GO ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [FST_FG] CONTAINS FILESTREAM
Add file to the FileStream filegroup
USE [master] GO ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'FTS_Doc_1', FILENAME = N'C:\dsf\FTS_Doc_1' ) TO FILEGROUP [FST_FG] GO
Configure Database for FileStream
USE [master] GO ALTER DATABASE [AdventureWorks2012] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'documents_dbprox' ) WITH NO_WAIT GO
Script to check databses enabled for Filestream option
SELECT DB_NAME(database_id),non_transacted_access,non_transacted_access_desc FROM sys.database_filestream_options where non_transacted_access >0; GO
Create FileTable Table
GO CREATE TABLE FileTableTb AS FileTable WITH (FileTable_Directory = 'documents_dbprox'); GO
Right click on the file table and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.
Copy files to the share \\Sinpc114\mssqlserver\documents_dbprox\documents_dbprox