Installer

This is a basic installation script – point it at a folder full of SQL script (.sql) files, and it will deploy them to the server you specify.

  1. Configure user-friendly aliases for server names in config.cmd.
  2. Run install.cmd, specifying the alias of your target server and the folder containing the scripts you want to deploy:

    install.cmd SERVERALIAS FolderName

Scripts are deployed in lexicographic filename order. You might find it useful give numeric prefixes to your script file names to make sure you know what's going to happen, e.g.:

Quiet mode

By default, Install.cmd prompts you for confirmation to continue a folder's deployment. You can suppress this by adding the flag -q to the end of the command:

install.cmd SERVERALIAS FolderName -q

This can be useful if you want to script out deployment of a collection of folders.

Install.cmd
@ECHO off

:: ******************************************************************
:: * Install.cmd - batch file installer for T-SQL scripts
:: * (C) 2013-2018 Richard Swinbank 
:: * http://richardswinbank.net/dos/installer
:: ******************************************************************
 
SETLOCAL EnableDelayedExpansion 

:: ******** check input variables and get others ********************
IF x%1==x GOTO usageerror
IF x%2==x GOTO usageerror
CALL config.cmd %1 %3
IF !ERRORLEVEL! NEQ 0 GOTO error

:: ******** check the script folder exists and is a directory *******
SET ScriptFolder=%2
IF NOT EXIST "%ScriptFolder%" GOTO foldererror
PUSHD "%ScriptFolder%"
IF !ERRORLEVEL! NEQ 0 GOTO foldererror

:: ******** check we can write to the log ***************************
ECHO %DATE% %TIME% Starting deployment from %ScriptFolder% to %DbServer% > %LogFile%
 
IF !ERRORLEVEL! NEQ 0 (
  ECHO Error writing to "%Logfile%"
  GOTO error
)

:: ******** get user confirmation to continue ***********************
ECHO Ready to deploy scripts
ECHO.
ECHO   FROM folder:  %ScriptFolder% 
ECHO.
ECHO   TO   server:  %DbServer%
ECHO.
ECHO   USING      :  %SqlCmd%
ECHO.
 
SET StartDeployment=No
IF %PROMPT%==NO (
  SET StartDeployment=Y
) ELSE (
  SET /P startDeployment=Enter Y ^& [Enter] to start deployment, [Enter] to abort:
)
IF NOT x%startDeployment%==xY GOTO abort

:: ******** deploy each script **************************************
:: "for %%f in (*.sql)..." should have the same effect, but name 
:: order is pretty critical here so we want it guaranteed!
 
FOR /f "tokens=*" %%f IN ('DIR /b /on *.sql') DO (
 
  ECHO.
  ECHO !DATE! !TIME! Deploying "%%f"
  ECHO !DATE! !TIME! Deploying "%%f" >> "%Logfile%"
 
  "%SqlCmd%" -I -E -S "%DbServer%" -i "%%f" -e -b >> "%Logfile%" 2>&1
 
  IF !ERRORLEVEL! NEQ 0 (
    ECHO   Error deploying script "%%f" - batch aborted
    GOTO deployerror
  )  
 
  ECHO ************************************************************** >> %Logfile%
)

:: ******** exit routes ***********************
SET exitMsg=Deployment completed at !DATE! !TIME! - see "%LogFile%" for details
SET exitCode=0
GOTO end
 
:abort
SET exitMsg=Deployment aborted at !DATE! !TIME!
SET exitCode=0
GOTO end
 
:usageerror
SET exitMsg=Usage %0 { targetAlias } { scriptFolder } 
GOTO error
 
:foldererror
SET exitMsg=Deployment script folder "%ScriptFolder%" not found
GOTO error
 
:deployerror
SET exitMsg=Deployment failed at !DATE! !TIME! - see "%LogFile%" for details
GOTO error
 
:error
SET exitCode=1
GOTO end
 
:end
POPD
ECHO.
IF NOT "%exitMsg%" == "" ECHO %exitMsg%
IF %PROMPT%==YES PAUSE
EXIT /b %exitCode%

This config file is called to set some variables at the start of Install.cmd. In here you configure server aliases – short, convenient names to refer to deployment target servers – and a default location for SQL Server's SQLCMD executable.

At runtime, the script translates the specified alias into the corresponding server name, attempts to determine SQLCMD's location dynamically, and validates the selected SQLCMD path.

Config.cmd
:: ********************************************************
:: * Config.cmd - configuration variables for Install.cmd
:: * (C) 2013-2018 Richard Swinbank 
:: * http://richardswinbank.net/dos/installer
:: ********************************************************

:: ******* target aliases ******
SET DEV=localhost\INSTANCE
SET TEST=MyTestServer\INSTANCE
SET PROD=MyProdServer\INSTANCE

 
:: ******** set log file name *****************************
SET LogFile=

:: check we have WMIC
WMIC.EXE Alias /? >NUL 2>&1 || GOTO setLogFile

:: We only want line 2 of WMIC's output - we're using FOR here because we
:: want the "tokens" functionality. "skip=1" starts from line 2, IF x%%L==x... 
:: breaks out at line 3 -- i.e. we only go round the loop once.
FOR /F "skip=1 tokens=1-6" %%G IN ('WMIC Path Win32_LocalTime Get Day^,Month^,Year^,Hour^,Minute^,Second /Format:table') DO (
  IF x%%L==x GOTO gotDateTokens
  SET yy=%%L
  SET dd=00%%G
  SET mm=00%%J
  SET hr=00%%H
  SET min=00%%I
  SET sec=00%%K
)
:gotDateTokens

:: reduce 00-padded strings to rightmost two characters
SET mm=%mm:~-2%
SET dd=%dd:~-2%
SET hr=%hr:~-2%
SET min=%min:~-2%
SET sec=%sec:~-2%
 
SET LogFile=_%yy%%mm%%dd%%hr%%min%%sec%
 
:setLogFile
SET LogFile=deploy%LogFile%.log

:: ******** set prompt options ****************************
SET PROMPT=YES
 
IF x%2==x GOTO targets
IF %2==-q (
  SET PROMPT=NO
  GOTO targets
)
ECHO Invalid option "%2"
GOTO error

:: ******** set deployment target *************************
:targets
SET DbServer=!%1!
 
IF x%DbServer%==x (
  ECHO Target alias "%1" not defined
  GOTO error
)

:: ******** find SQLCMD executable ************************
SET SqlCmd=C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE
WHERE SQLCMD > sqlcmdpath.txt 2>&1
IF !ERRORLEVEL! EQU 0 SET /P SqlCmd=<sqlcmdpath.txt  
DEL sqlcmdpath.txt
 
IF NOT EXIST "%SqlCmd%" (
  ECHO SQLCMD executable "%SqlCmd%" not found
  GOTO error
)
 
SET exitCode=0
GOTO end
 
:error
SET exitCode=1
GOTO end
 
:end
EXIT /b %exitCode%