Installing SQL Server can be quite a long and tedious task, so instead of doing this manually, an alternative is to use Chocolatey. The package that is available from Chocolatey is pre-configured with a set of common default settings.
# ------------------------------------------------------------------------------
# Installing a local instance of SQL Server Express
#
# As mentioned in this article on Continuous Integration, we recommend that you
# use a local instance of SQL Server with gated deployment. This ensures that
# there are no collisions between build agents (i.e. if you are running multiple
# simultaneous builds on different build agents).
#
# Installing SQL Server can be quite a long and tedious task, so instead of
# doing this manually, an alternative is to use Chocolatey. The package that is
# available from Chocolatey is pre-configured with a set of common default
# settings.
#
# To install SQL Server 2012 Express Edition using Chocolatey, open a command
# prompt in administrator mode and run the following (not that this will also
# retrieve Chocolatey if you don't already have it installed):
#
# Source: https://documentation.red-gate.com/display/RR1/Installing+a+local+instance+of+SQL+Server+Express
# ------------------------------------------------------------------------------
#
# These commands will take a while to run, but you shouldn't have to intervene
# or confirm any dialog boxes, and a restart should not be required.
@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin
CALL cinst MsSqlServer2012Express
#
# Ensures that your build agent can access the newly-installed SQLEXPRESS
# instance (which installs as the default SQL Server instance on the machine).
# You might choose to make this access more restrictive if you know which user
# your build agent will run-as.
@echo Adding [BUILTIN\Users] to sysadmin role on local SQL instance
"%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S . -E -Q "ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Users];"