Pre-deployment script to add a filegroup and a file

/*
Pre-Deployment Script Example
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be prepended to the build script.
 This example requires SQLCMD syntax 
 For more information, see https://documentation.red-gate.com/display/sca4/Pre-deployment+and+post-deployment+scripts
--------------------------------------------------------------------------------------
*/

/* This may be be uncommented and edited to use a SQLCMD variable for testing in SSMS 
under SQLCMD mode (Query menu -> SQLCMD), but remove or comment this out before committing. */
--:setvar DatabaseName YourDatabaseName


SET XACT_ABORT, NOCOUNT ON;
GO

USE [$(DatabaseName)];
GO

BEGIN TRY

    DECLARE @fg sysname = N'ExampleFileGroup',
            @filename NVARCHAR(1000) = N'',
            @path NVARCHAR(1000) = N'C:\MSSQL\DATA\',
            @msg NVARCHAR(2048) = N'',
            @physicalName NVARCHAR(1000) = N'',
            @dynamicSQL NVARCHAR(MAX) = N'';

    /* Guard clauses like this may prevent or allow deployment if needed */
    IF @@SERVERNAME = N'SERVERNAME-TO-BLOCK'
    BEGIN
        SET @msg = N'SERVERNAME is ' + @@SERVERNAME + N', no action taken.';
        RAISERROR(@msg, 0, 0) WITH NOWAIT;
    END;
    ELSE
    BEGIN

        /* It can be useful to uniquify filenames to enable concurrent builds, etc*/
        SET @filename = N'ExampleFileName' + N'$(DatabaseName)';
        SET @physicalName = @path + @filename + N'.ndf';

        IF EXISTS (SELECT * FROM sys.filegroups WHERE name = @fg)
        BEGIN
            SET @msg = N'Filegroup ' + @fg + N' exists in [$(DatabaseName)], no action taken.';
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
        END;
        ELSE
        BEGIN
            SET @msg = N'Adding filegroup ' + @fg + N' to database [$(DatabaseName)].';
            RAISERROR(@msg, 0, 0) WITH NOWAIT;

            SET @dynamicSQL = N'ALTER DATABASE CURRENT ADD FILEGROUP ' + QUOTENAME(@fg);
            --PRINT @dynamicSQL;
            EXEC (@dynamicSQL);
        END;

        /*For simplicity we're checking only for existence of the file by physical name, not
	looking at filename and/or what filegroup it is in. You may chose to make this more thorough. */
        IF EXISTS (SELECT * FROM sys.database_files WHERE name = @filename)
        BEGIN
            SET @msg = N'File with name ' + @filename + N' exists in database ' + N'$(DatabaseName), no action taken.';
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
        END;
        ELSE
        BEGIN
            SET @msg = N'Adding file with  name ' + @filename + N' to ' + N'$(DatabaseName).';
            RAISERROR(@msg, 0, 0) WITH NOWAIT;


            SET @dynamicSQL
                = N'
            ALTER DATABASE CURRENT
            ADD FILE
                (
                    NAME = ''' + @filename + N''',
                    FILENAME = ''' + @physicalName + N'''
                )
            TO FILEGROUP ' + QUOTENAME(@fg);

            --PRINT @dynamicSQL;
            EXEC (@dynamicSQL);
        END;
    END;

END TRY
BEGIN CATCH
    IF @@trancount > 0
        ROLLBACK TRANSACTION;
    SET @msg = ERROR_MESSAGE();
    RAISERROR(@msg, 16, 1);

END CATCH;

Pre-deployment script to add a filegroup and a file using a dynamic path based on the results of a query

/*
Pre-Deployment Script Example
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be prepended to the build script.
 This example requires SQLCMD syntax 
 For more information, see https://documentation.red-gate.com/display/sca4/Pre-deployment+and+post-deployment+scripts
--------------------------------------------------------------------------------------
*/

/* This may be be uncommented and edited to use a SQLCMD variable for testing in SSMS 
under SQLCMD mode (Query menu -> SQLCMD), but remove or comment this out before committing. */
--:setvar DatabaseName YourDatabaseName


SET XACT_ABORT, NOCOUNT ON;
GO

USE [$(DatabaseName)];
GO

BEGIN TRY


    DECLARE @filename sysname = N'',
            @msg NVARCHAR(1000) = N'',
            @dsql NVARCHAR(MAX) = N'';
    SET @filename = '$(DatabaseName)' + N'_fs';


    /* Check for the existence of a filegroup based on the name. This example uses the SQLCMD scripting variable */
    IF EXISTS
    (
        SELECT *
        FROM [$(DatabaseName)].sys.filegroups
        WHERE name = '$(DatabaseName)_fg'
    )
    BEGIN
        RAISERROR('Filegroup [$(DatabaseName)_fg] exists in database [$(DatabaseName)], no action taken.', 0, 0) WITH NOWAIT;

    END;
    ELSE
    BEGIN
        RAISERROR(N'Adding filegroup [$(DatabaseName)_fg] to database [$(DatabaseName)]', 0, 0) WITH NOWAIT;
        ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP [$(DatabaseName)_fg];
    END;



    /* SQLCMD scripting variables don't embed in strings easily because of the way they are processed.
	The following pattern helps work around this. */


    IF EXISTS
    (
        SELECT *
        FROM [$(DatabaseName)].sys.database_files
        WHERE name = @filename
    )
    BEGIN
        SET @msg = N'File ' + @filename + N' exists in database ' + N'$(DatabaseName), no action taken.';
        RAISERROR(@msg, 0, 0) WITH NOWAIT;
    END;
    ELSE
    BEGIN
        SET @msg = N'Adding file ' + @filename + N' to database ' + N'$(DatabaseName)_fg';
        RAISERROR(@msg, 0, 0) WITH NOWAIT;

        SET @dsql
            = N'
    ALTER DATABASE [#DBNAME#]
    ADD FILE
        (
            NAME = ''' + @filename
              + N''',
            FILENAME = ''#PATH##DBNAME#_fg''
        )
    TO FILEGROUP [#DBNAME#_fg];
'       ;
        SET @dsql = REPLACE(@dsql, '#DBNAME#', '$(DatabaseName)');
        SET @dsql = REPLACE(@dsql, '#PATH#', CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(1000)));

        --PRINT @dsql;
        EXEC (@dsql);

    END;

END TRY
BEGIN CATCH
    IF @@trancount > 0
        ROLLBACK TRANSACTION;
    SET @msg = ERROR_MESSAGE();
    RAISERROR(@msg, 16, 1);

END CATCH;