[SOLVED] Adding a file to FileGroup raises error while creating database with FileGroup file works fine

I use SQL Server 2022 and want to add a file group with file to an existing database. I also have a SQL Server 2019 running on my computer.

At the beginning I had problems because I installed SQL Server as admin and than started SSMS as 'normal' user on my notebook. So I execute SSMS as admin. Then I figured out that the SQL Server user has no access to my local directory, that's why I save all the files on users/public...

I use:

Microsoft SQL Server Express (64-bit) / 16.0.1125.1
On this Version 'AlwaysOn' cannot be activated
FileStream is activated on Server Level
FileStream access level on database is 'FULL'
FileGroup exists (when trying to add File to FileGroup)

This works.

USE master
GO

CREATE DATABASE [Q8]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'Q8', FILENAME = N'C:\Users\Public\SQLEXPRESS01\Q8.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [FileGroup_SQLEXPRESS01_Q8] CONTAINS FILESTREAM  DEFAULT
( NAME = N'FileGroup_Q8', FILENAME = N'C:\Users\Public\SQLEXPRESS01\FileGroup_Q8' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'Q8_log', FILENAME = N'C:\Users\Public\SQLEXPRESS01\Q8_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Q8].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Q8] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL ) 
GO

Here is my script to create database and add afterward the FileGroup and the File to FileGroup

USE [master]
GO

 :SETVAR DatabaseName [Q80]

PRINT N'Run script on: ' + DB_NAME();

DECLARE @ProductVersion SQL_VARIANT = NULL;
DECLARE @ProductLevel SQL_VARIANT = NULL;
DECLARE @Edition SQL_VARIANT = NULL;
SELECT @ProductVersion = SERVERPROPERTY('productversion')
      ,@ProductLevel = SERVERPROPERTY('productlevel') 
      ,@Edition = SERVERPROPERTY('edition')
;
PRINT N'@ProductVersion: ' + TRIM(CAST(@ProductVersion AS NVARCHAR(100)))
PRINT N'@ProductLevel: ' + TRIM(CAST(@ProductLevel AS NVARCHAR(100))) 
PRINT N'@Edition: ' + TRIM(CAST(@Edition AS NVARCHAR(100))) 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- SELECT * FROM sys.databases WHERE database_id = db_id(TRIM('[]' FROM '$(DatabaseName)'))

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE database_id = db_id(TRIM('[]' FROM '$(DatabaseName)')))
  CREATE DATABASE $(DatabaseName) WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO
-- =============================================
-- Author:      Eva-Maria Segiet
-- Create date: 2021-06-24
-- Description: requirement: dba enabled filetable on server; preparing db for filetable...
-- Copyright (C) 2021 SPEKTRA GmbH Dresden
-- =============================================
USE $(DatabaseName)
DECLARE @Ret sql_variant;

-- EXEC @Ret = sp_configure 'show advanced options'
SELECT @Ret  = [value]  FROM sys.configurations  WHERE [name] = N'filestream access level'
PRINT CAST(@Ret AS NVARCHAR(10))
IF @Ret != 2
BEGIN
  EXEC sp_configure filestream_access_level, 2
  PRINT 'updated filestream_access_level to 2...'
  RECONFIGURE
END
ELSE
  PRINT 'filestream_access_level is already on 2...'
GO

-- SELECT db_id(TRIM('[]' FROM '$(DatabaseName)'))
-- SELECT * FROM sys.database_filestream_options WHERE database_id = db_id(TRIM('[]' FROM '$(DatabaseName)'))
-- SELECT * FROM sys.database_filestream_options WHERE database_id = db_id(TRIM('[]' FROM '$(DatabaseName)')) AND non_transacted_access_desc  = N'FULL'
IF NOT EXISTS (SELECT 1 FROM sys.database_filestream_options WHERE database_id = db_id(TRIM('[]' FROM '$(DatabaseName)')) AND non_transacted_access_desc  = N'FULL')
BEGIN
  PRINT N'Setting NON_TRANSACTED_ACCESS = FULL...'

  DECLARE @db_name NVARCHAR(128) = TRIM('[]' FROM '$(DatabaseName)')
         ,@server_name NVARCHAR(128) = REPLACE(REPLACE(@@SERVERNAME, '-', '_'), '\', '_')
         ,@SQLString VARCHAR(2000)
  ;
  --SELECT @server_name = REPLACE(@server_name, STRING_ESCAPE('\', 'json'), '_');
  PRINT @server_name
  PRINT @db_name
  SET @SQLString = CONCAT(N'ALTER DATABASE [', @db_name, '] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL )'
                         );
  PRINT @SQLString;
  EXECUTE( @SQLString );
END
GO
    -- >> reconnect to server / restart server

DECLARE @db_name NVARCHAR(128) = NULL -- '$(DatabaseName)'
       ,@server_name NVARCHAR(128) = @@SERVERNAME
         ,@SQLString VARCHAR(2000)
         ,@data_name NVARCHAR(128)
         ,@data_filename VARCHAR(2000)
         ,@filegroup NVARCHAR(128)
         ,@log_name NVARCHAR(128)
         ,@log_filename VARCHAR(2000)
         ,@filegroup_name NVARCHAR(128)
         ,@filegroup_filename VARCHAR(2000)
         ,@ContainerName VARCHAR(2000)
;

SELECT @db_name = TRIM('[]' FROM '$(DatabaseName)');
PRINT @db_name
SET @data_filename = CONCAT(N'C:\Users\Public\',@server_name,'\',@db_name,'.mdf');
SET @log_name = CONCAT(@db_name,N'_log')
SET @log_filename = CONCAT(N'C:\Users\Public\', @server_name,'\',@db_name,N'_log.ldf')
SET @filegroup = CONCAT(N'FileGroup_',REPLACE(REPLACE(@server_name, '\', '_'),'-','_'),'_',@db_name);
SET @filegroup_name = CONCAT(N'FileGroup_',@db_name);
SET @filegroup_filename = CONCAT(N'C:\Users\Public\',REPLACE(REPLACE(@server_name, '\', '_'),'-','_'),'\FileGroup_',@db_name,'\');

PRINT N'FileGroup: ' + @filegroup

-- Add a filegroup to the database using the following command
SELECT *, FILEGROUP_NAME(data_space_id) FROM sys.filegroups WHERE [name] = @filegroup
IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE [name] = @filegroup)
BEGIN
  PRINT N'Add FileGroup [' + @filegroup + '] CONTAINS FILESTREAM'
  SET @SQLString = CONCAT(N'ALTER DATABASE [', @db_name,'] ADD FILEGROUP [', @filegroup, '] CONTAINS FILESTREAM');
  PRINT @SQLString;
  EXECUTE( @SQLString )
END
ELSE 
  PRINT N'Aleady existing FileGroup [' + @filegroup + '] CONTAINS FILESTREAM'

-- Add a file to the database using the following command
SELECT * FROM sys.database_files
IF NOT EXISTS (SELECT 1 FROM sys.database_files WHERE type = 2 AND [name] = @filegroup_name)
  BEGIN
    SET @SQLString = CONCAT(N'ALTER DATABASE [', @db_name, N'] '
                           ,N'ADD FILE ( NAME = "', @filegroup_name, '", FILENAME = "', @filegroup_filename, '" ) '
                           ,N',( NAME = "', @filegroup_name, '_2", FILENAME = "', RTRIM(@filegroup_filename,'\'), '_2" )  TO FILEGROUP [', @filegroup, N']'
                           );
    PRINT @SQLString;
    EXECUTE( @SQLString )
  END

SELECT * FROM sys.database_files WHERE type = 2 AND [name] = @filegroup_name
GO

I get this error:

Meldung 35221, Ebene 16, Status 1, Zeile 84
Der Vorgang konnte nicht verarbeitet werden. Der Manager für AlwaysOn-Verfügbarkeitsgruppenreplikate ist in dieser SQL Server-Instanz deaktiviert. Aktivieren Sie AlwaysOn-Verfügbarkeitsgruppen mithilfe des SQL Server-Konfigurations-Managers. Starten Sie dann den SQL Server-Dienst neu, und wiederholen Sie den aktuellen Vorgang. Informationen zum Aktivieren und Deaktivieren von AlwaysOn-Verfügbarkeitsgruppen finden Sie in der SQL Server-Onlinedokumentation.

I also tried to execute script without dynamic SQL :

SELECT *, FILEGROUP_NAME(data_space_id) FROM sys.filegroups
--> FileGroup [FileGroup_GDD_NO_20_002_SQLEXPRESS01_Q80] exists (type=N'FD')
ALTER DATABASE [Q80]
ADD FILEGROUP [FileGroup_GDD_NO_20_002_SQLEXPRESS01_Q80]
CONTAINS FILESTREAM;
GO

--Add a file for storing database photos to FILEGROUP
ALTER DATABASE [Q80]
ADD FILE
(
  NAME= 'FileGroup_Q80',
  FILENAME = 'C:\Users\Public\GDD-NO-20-002\SQLEXPRESS01\FileGroup_Q80\'
  -- FILENAME = 'C:\Users\Public\GDD-NO-20-002\SQLEXPRESS01\FileGroup_Q80\Q80.ndf'
)
TO FILEGROUP [FileGroup_GDD_NO_20_002_SQLEXPRESS01_Q80];
GO

Result:

Meldung 35221, Ebene 16, Status 1, Zeile 132
Der Vorgang konnte nicht verarbeitet werden. Der Manager für AlwaysOn-Verfügbarkeitsgruppenreplikate ist in dieser SQL Server-Instanz deaktiviert. Aktivieren Sie AlwaysOn-Verfügbarkeitsgruppen mithilfe des SQL Server-Konfigurations-Managers. Starten Sie dann den SQL Server-Dienst neu, und wiederholen Sie den aktuellen Vorgang. Informationen zum Aktivieren und Deaktivieren von AlwaysOn-Verfügbarkeitsgruppen finden Sie in der SQL Server-Onlinedokumentation.