Create Blank TeamCity Database.sql

:on error exit
:setvar databaseName TeamCity
:setvar serviceAccount DOMAIN\ACCOUNT
/*
	Creates a blank database suitable for use with TeamCity
	See https://confluence.jetbrains.com/display/TCD9/Setting+up+an+External+Database#SettingupanExternalDatabase-MicrosoftSQLServer

	Important bit is to get the case-sensitive colation correct (_CS_AS),
	which is required for non-Windows build agents (if ever required in future)

	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.
	JetBrains recommend not setting a log size <1GB

	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_CS_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)]
Enter file contents here