Categories
Scripts

Batch script to deploy multiple SQL files

I was working on a project where I’m supposed to deploy lots of SQL Server script files (.sql). It’s very tedious job either to manually execute or to write a batch script each time when the deployment moves to another environment. So I decided to write a batch script which should be dynamic in nature so that I can easily use this script across any SQL Script files.

Same script is rewritten using powershell, check http://sql-articles.com/scripts/powershell/batch-script-to-deploy-multiple-sql-files-version-2/ for updated version

Update Date : 29-Apr-2018

This batch file is capable of executing all .sql files in a folder which includes sub directory. It executes the script in alphabetical order so our challenge is to order the files. I have used the below folder structure so that the dependency objects are first created. For instance tables are the first object that need to be created then followed by triggers, views or stored procedure. So you can also organize your scripts accordingly

batch_script_1

Batch Script

@echo off
setlocal enabledelayedexpansion
set /p servername=Enter DB Servername :
set /p dbname=Enter Database Name :
set /p spath=Enter Script Path :
set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
set cmd='dir %spath%\*.sql /b/s'
FOR /f %%G IN (%cmd%) DO (
echo ******PROCESSING %%G FILE******
echo ******PROCESSING %%G FILE****** >> %logfilepath%
SQLCMD -S%servername% -E -d%dbname% -b -i%%G >> %logfilepath%
IF !ERRORLEVEL! NEQ 0 GOTO :OnError
)
GOTO :Success

:OnError
echo ERROR ERROR ERROR
echo One\more script(s) failed to execute, terminating bath.
echo Check output.log file for more details
EXIT /b

:Success
echo ALL the scripts deployed successfully!!
EXIT /b

batch_script_2

You can copy the above script and paste it in a notepad and save it as batch.bat. Now execute the batch file and then provide the parameter values , it will automatically pick all the files from the folder specified and execute it. This script will also create a output log file which has all the information about which script it executed and it’s result (from SQL Server result set). In addition to this if any one of the .sql file fails in between to execute, then the batch file will stop there. You need to check the output log file for more information on where it failed and the error details.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*