SQL SPROC - Enable FILESTREAM and Create FILESTREAM Database

SP_CreateFileStreamDatabase.sql

GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		https://github.com/alexhiggins732
-- Create date: 5/18/2018
-- Description:	Stored Procedure to Create a FILESTREAM enabled DATABASE. See the comments for instructions to enable FILESTREAM on the server
-- =============================================
CREATE PROCEDURE SP_CreateFileStreamDatabase 

	@DatabaseName nvarchar(100),
	@RootDirectory nvarchar(250),
	@GenerateOnly bit = 1
AS
BEGIN

/*
	1) If not enabled, In SQL Server Configuration Manager locate the instance under SQL Services, right click and choose properties.
		Select the FILESTREAM tab and check Enable FILESTREAM fo TSQL and IO. Optionally check allow remote access. 
			For detailed instructions and best practices see: https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream?view=sql-server-2017

	2) If not already configured, on the instance run:
		EXEC sp_configure filestream_access_level, 2  
		RECONFIGURE  
	3) In sql2014 2) didn't apply the settings. Instead, from the DB Properties pages-> Options FileStream_NonTransacted_Access
		was set to full and the directory name specified. Docs show this can be done with the following command but
		in attempting to run it it never completes because the statement needs all connections closed.
			ALTER DATABASE DbFiles  
    			SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' )  
	4) File Tables can then be created via T-SQL:
		CREATE TABLE UserRoot AS FileTable;  

		CREATE TABLE DocumentStore AS FileTable  
		    WITH (   
			  FileTable_Directory = 'DocumentTable',  
			  FileTable_Collate_Filename = database_default  
			 );  
          Or via windows explorer \\ServerName\InstanceName\Database\
GO  
*/

    -- NORMALIZE THE ROOT DIRECTORY
	IF (RIGHT(@RootDirectory, 1) <> '\')
	BEGIN
		SET @RootDirectory= @RootDirectory + '\';
	END


	DECLARE @DataFileExt NVARCHAR(4)= '.mdf';
	DECLARE @DatFileName NVARCHAR(100)='data1';
	DECLARE @S_PrimaryGroup NVARCHAR(100)= 'PrimaryGroup';
	DECLARE @PrimaryFileStreamGroup NVARCHAR(100)= @S_PrimaryGroup + '1';
	declare @S_FileStream  nvarchar(100)= 'FileStream';
	DECLARE @FileStreamFileName NVARCHAR(100)=@S_FileStream  + '1';
	declare @S_Log NVARCHAR(100)= 'log';
	declare @FileStreamLogname NVARCHAR(100)= @S_Log + '1';
	declare @LogExt NVARCHAR(4)='.ldf'

	--Declare @DatabaseName NVARCHAR(100) = 'DbFiles';
	DECLARE @Quote NVARCHAR(1)= '''';
	DECLARE @TSQL nvarchar(4000)='';

	

	SET @TSQL= '
	CREATE DATABASE ' + @DatabaseName + ' 
	ON
	PRIMARY ( NAME = ' + QUOTENAME(@DatabaseName +  'Primary') + ',
		FILENAME = ' + @Quote + @RootDirectory + @DatabaseName + @DatFileName + @DataFileExt + @Quote + '),
	FILEGROUP ' + QUOTENAME(@DatabaseName + @PrimaryFileStreamGroup) + '
		CONTAINS 
			FILESTREAM
			(
				NAME = ' + QUOTENAME(@DatabaseName +  @S_FileStream + @S_PrimaryGroup) + ',
				FILENAME = ' + @Quote + @RootDirectory + @DatabaseName + @S_FileStream + @FileStreamFileName +  @Quote + '
			)
		LOG ON  
			(
				NAME = ' + QUOTENAME(@DatabaseName +  @S_FileStream + @S_Log) + ',
				FILENAME = ' + @Quote + @RootDirectory + @DatabaseName + @S_FileStream + @FileStreamLogname+ @LogExt + @Quote + '
			)
	'

	IF (@GenerateOnly=0)
	BEGIN
		EXEC SP_EXECUTESQL @tSQL
	END

	SELECT @TSQL;

END
GO