pre-deployment_WWI.sql

IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name='USERDATA')BEGIN
    PRINT N'Adding filegroup USERDATA to database';
    ALTER DATABASE CURRENT ADD FILEGROUP USERDATA;
END;
GO

IF @@ERROR<>0 SET NOEXEC ON;
GO


IF NOT EXISTS (SELECT * FROM sys.database_files WHERE name='WWI_UserData')BEGIN
    IF @@SERVERNAME='GATEBASE01\DEV01' BEGIN

    DECLARE @fname nvarchar(1000) = N'', @dsql NVARCHAR(MAX) = N'';

    SELECT @fname = N'BUILD_WideWorldImporters_SOC_CI_UserData_' + DB_NAME() + '.ndf';

    SET @dsql = N'
            ALTER DATABASE CURRENT
            ADD FILE(NAME=WWI_UserData, FILENAME=''C:\MSSQL\Data\' +  @fname  + N''')
            TO FILEGROUP USERDATA;'

        PRINT N'Adding file WWI_UserData to filegroup USERDATA';
        EXEC ( @dsql)
    END;
END;
GO

IF @@ERROR<>0 SET NOEXEC ON;
GO

IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name='WWI_InMemory_Data')BEGIN
    PRINT N'Adding filegroup WWI_InMemory_Data to database';
    ALTER DATABASE CURRENT
    ADD FILEGROUP WWI_InMemory_Data
    CONTAINS MEMORY_OPTIMIZED_DATA;
END;
GO

IF @@ERROR<>0 SET NOEXEC ON;
GO

IF NOT EXISTS (SELECT * FROM sys.database_files WHERE name='WWI_InMemory_Data_1')BEGIN
    IF @@SERVERNAME='GATEBASE01\DEV01' BEGIN

    DECLARE @fname nvarchar(1000) = N'', @dsql NVARCHAR(MAX) = N'';

    SELECT @fname = N'BUILD_WideWorldImporters_InMemory_Data_1_SOC_CI_' + DB_NAME();

    SET @dsql = N'
        ALTER DATABASE CURRENT
        ADD FILE ( NAME=''WWI_InMemory_Data_1'', FILENAME=''C:\MSSQL\Data\' + @fname + ''')
        TO FILEGROUP WWI_InMemory_Data;'

        PRINT N'Adding file WWI_InMemory_Data_1 to filegroup WWI_InMemory_Data';
        EXEC sp_executesql @dsql 

    END;
END;
GO