Pre-Deployment script to set the database Recovery Model to Simple

ALTER DATABASE CURRENT SET RECOVERY SIMPLE
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

Pre-Deployment script to set the database Collation to Danish

ALTER DATABASE CURRENT COLLATE Danish_Norwegian_CI_AS
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

Pre-Deployment script to add a Filegroup and File

IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = 'MyFileGroup')
BEGIN
    PRINT N'Adding filegroup MyFileGroup to database'
    ALTER DATABASE CURRENT ADD FILEGROUP MyFileGroup
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
 
IF NOT EXISTS (SELECT * FROM sys.database_files WHERE name = 'MyFile')
BEGIN
    IF @@SERVERNAME = 'DEV-DEVELOPERNAME'
    BEGIN
        PRINT N'Adding file MyFile to filegroup MyFileGroup'
        ALTER DATABASE CURRENT
        ADD FILE
            (
                NAME = MyFile,
                FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyFile.mdf'
            )
        TO FILEGROUP MyFileGroup
    END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

Notable points:

• Pre-Deployment scripts must be re-runnable as they will be executed as part of every deployment.
  • Object existence checks are used for both MyFileGroup and MyFile to ensure they are not created twice.
• Pre-Deployment scripts are deployed to every environment.
  • CURRENT is used because the database may have different names in different environments.
  • Other environment specific differences such as file paths may need to change between servers. Here, we use @@SERVERNAME to allow environment specific paths.
• By default there is no error handling, so this script tests for an error, and sets NOEXEC to ON if an error occurs.