Create Octopus Deploy Database.sql

:on error exit
:setvar databaseName OctopusDeploy
:setvar serviceAccount DOMAIN\ACCOUNT
/*
	Creates a blank database suitable for use with Octopus Deploy
	See http://docs.octopusdeploy.com/display/OD/SQL+Server+Database+Requirements
	Important bits:
		- must be CASE INSENSITIVE
		- must setup Octopus service account as dbo_owner

	Initial file sizes are just a guess, but want to *not* have default 1mb growths etc...
	This is VERY important for the TLog, as affects number of log extents.

	NB: Recovery model will be default for that SQL instance (ie as per MODEL database)
	This should be FULL in production, but probably SIMPLE everywhere else
	--
	PW 2015
*/
IF EXISTS(select * from master.sys.databases where name = '$(databaseName)')
	-- This requires on error exit above, as in normal TSQL mode subsequent batches will still run
	RAISERROR('Target database already exists',16,1);
GO

CREATE DATABASE [$(databaseName)] COLLATE Latin1_General_CI_AS;
GO

ALTER DATABASE [$(databaseName)] MODIFY FILE ( NAME = N'$(databaseName)', SIZE = 1GB, FILEGROWTH = 1GB );
ALTER DATABASE [$(databaseName)] MODIFY FILE ( NAME = N'$(databaseName)_log', SIZE = 1GB, FILEGROWTH = 1GB );

ALTER DATABASE [$(databaseName)] SET COMPATIBILITY_LEVEL = 110;
ALTER DATABASE [$(databaseName)] SET PAGE_VERIFY CHECKSUM  ;

IF NOT EXISTS (SELECT name FROM [$(databaseName)].sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') 
	ALTER DATABASE [$(databaseName)] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

USE [$(databaseName)]

IF NOT EXISTS(select * from sys.database_principals p where p.name = '$(serviceAccount)')
    BEGIN
        IF NOT EXISTS(select * from master.sys.server_principals p where p.name = '$(serviceAccount)')
        CREATE LOGIN [$(serviceAccount)] from Windows;
        CREATE USER [$(serviceAccount)] for login [$(serviceAccount)];
    END   
    ALTER ROLE [db_owner] add member [$(serviceAccount)]