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.
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
@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
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.