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 https://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.

Posted

June 16, 2013

Scripts

VidhyaSagar

Tags:

bat, deploy, deployment, implementation, scripts, sql

Comments

44 responses to “Batch script to deploy multiple SQL files”

Ramesh

June 17, 2013

VidhyaSagar, Could you please provide the batch scripts to extract table data using bcp out into multiple files dynamically from a database

Reply
1. VidhyaSagar
  
  June 18, 2013
  
  Do you want a batch file to script out all the tables in the database using bcp?
  
  Reply
2. VidhyaSagar
  
  June 26, 2013
  
  @Ramesh — You can use the below batch file to script all the tables in a database. I have made bcp to script it in native file so that it can handle blob file types perfectly.. If you want you can add or remove parameters for bcp command.
  
  @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 sqlcmd.exe -E -S%servername% -d%dbname% -y0 -h-1 -Q "SET NOCOUNT ON; SELECT STUFF((SELECT ' ' + SCHEMA_NAME(schema_id) +'.'+name FROM sys.tables ORDER BY name FOR XML PATH('')),1,1,'')" > tmp.txt set /p table_list_import=< tmp.txt del tmp.txt SET table_list=(%table_list_import%) for %%i in %table_list% do ( echo ******PROCESSING %%i Table****** echo ******PROCESSING %%i Table****** >> %logfilepath% bcp %dbname%.%%i out "%spath%\%%i.csv" -T -n -S%servername% -b10000 -r\n -t, >> %logfilepath% IF !ERRORLEVEL! NEQ 0 GOTO :OnError )
  :OnError Echo "Unable to BCP out, check log file for additional details" Exit /b
  
  Reply
akash singh

August 6, 2014

Hi,

i used the above scripts ,but it does not work if the folder specified has empty spaces in the name.

eg.
K:\Deployement utility\Deploy\

here Deployement utility has an empty space in between.

please let me know if this can be handled.

Thanks,
Akash.

Reply
1. VidhyaSagar
  
  August 9, 2014
  
  @Akash – Use double quotes for the path or use 8 character folder name (you can obtain by executing dir C: /x)
  
  Reply
Gary

May 18, 2015

it didn’t work for me the script gets stuck at the first table. Any ideas???

Reply
1. VidhyaSagar
  
  May 22, 2015
  
  @Gary – Do you have space in the folder name where you placed the scripts? If yes make sure you dont have space in the path.
  
  Reply
Nandhini

May 22, 2015

i tried the above code but failed to transfer the objects to another server. i got an error ‘login failed’. VidhyaSagar please help on this.

Reply
1. VidhyaSagar
  
  May 22, 2015
  
  @Nandhini –
  In the script I used windows authentication at SQLCMD line, if you are connecting with sql authentication then replace parameter -E with -Uusername and -Ppassword
  
  Reply
Bunny

July 5, 2015

Hi Vidhya,

Can you please provide me a sql script which will call other scripts from different location with different schema and when anywhere any prompt comes so it will not exit. As i m trying to make it but getting exceptions and when any prompt comes so it gets exit.

Please share with me if you have any or help me how to make it.

Reply
1. VidhyaSagar
  
  July 15, 2015
  
  Can you send me some sample scripts [how the file name will be for different schemas] to my email so that I can modify and send it across.
  
  Reply
kittu

July 17, 2015

HI Vidya sagar,
in the similarway we have one script to update tables…and all kost all 300 ddls we used to run…if any ddl fails also it will run the executin…and we need to check the logs for failed scripts…is there any way to route the failed logs into different folder

Reply
1. VidhyaSagar
  
  July 30, 2015
  
  Yes kittu you can point failure logs to seperate file. In the error section you can do that. Let me know if you need any help.
  
  Thanks
  
  Reply
senthilkumar

August 12, 2015

Thanks Vidhi,

Your “Batch script to deploy multiple SQL files” is very useful for me..

Reply
1. VidhyaSagar
  
  September 2, 2015
  
  That’s good to hear
  
  Reply
Abhijeet Kamble

August 13, 2015

is there a way to call multiple scripts(.sql) without having to type exit through automation?

Reply
1. VidhyaSagar
  
  September 2, 2015
  
  No there is no way, for each time you need to call SQLCMD. Or else you need to consolidate all the scripts into one file.
  
  Reply
Arjith

September 16, 2015

Hi VidhyaSagar,

The above batch file works fine..As said if any error occurs during the execution the batch file will stop there, and we’ll have to rectify the error and run the batch file again..so doing this will execute the already executed scripts in the folder again if we don’t remove them from the folder right?

Reply
1. VidhyaSagar
  
  October 16, 2015
  
  Yes Arjith.. It will execute the scripts again.
  What you can do is just create another folder and move the files to that folder on successful completion
  
  Reply
Nandhini

October 19, 2015

@VidhyaSagar

I replaced parameter -E with -Uusername and -Ppassword in the first code but neither am getting any error message nor the objects hasn’t moved to the database as well.. please share your thoughts on this

Reply
Brijmohan

October 28, 2015

Hi VIDHYASAGAR,

When I execute this script it’s raise an error “Sqlcmd: ‘2’: Invalid filename.” , would you please help me.

Thanks in Advance

Reply
1. VidhyaSagar
  
  October 29, 2015
  
  Do you have a space file name or folder name? If yes make sure you remove the spaces.
  I may upload new script which can handle spaces but that will take some time, got little busy with other things.
  
  Reply
Abdul Kalam

May 4, 2016

Thanks VidhyaSagar , you saved my day . If possible can you post same implementation using PowerShell.

Reply
1. VidhyaSagar
  
  May 23, 2016
  
  @Abdul – As of now I dont have powershell version.. I’ll try to write it
  
  Reply
  1. Abdul Kalam
    
    May 26, 2016
    
    Hi VidhyaSagar,
    
    I am trying to add username , password to your script …its throwing Unexpected Argument error. Could you help me on this
    
    @echo off
    setlocal enabledelayedexpansion
    set /p servername=Enter DB Servername :
    set /p dbname=Enter Database Name :
    set /p username=Enter User Name :
    set /p password=Enter Password :
    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% -d%dbname% -u%username% -p%password% -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
    timeout /t 10
    EXIT /b
    
    :Success
    echo ALL the scripts deployed successfully!!
    timeout /t 10
    EXIT /b
    
    Reply
    1. DKing
      
      July 12, 2017
      
      replace:
      SQLCMD -S%servername% -d%dbname% -u%username% -p%password% -b -i%%G >> %logfilepath%
      
      with
      SQLCMD -S%servername% -d%dbname% -U%username% -P%password% -b -i%%G >> %logfilepath%
      
      Reply
Deepak

May 25, 2016

Hi Vidhya,
I am executing the same script for multiple sql files from my desktop to server (SSMS in server). But after processing only first file is it getting exit and I can see the changes in db for only file rest of them are not executing.

Reply
VidhyaSagar

May 25, 2016

Hi Deepak,

No space should be there in folder name or file name. Can you try to keep all the files in C or D drive and then try to call it. Did you see any errors?

Reply
1. ivan
  
  March 16, 2018
  
  hi the code is not picking up the next file if there is also no space
  
  Reply
  1. VidhyaSagar
    
    April 8, 2018
    
    I wrote another set of scripts similar to this using PowerShell. I’ll write a new article later and refer to it.
    
    Reply
santhosh

July 26, 2016

hi Vidhya, I am using the same script for oracle database by changing SQLCMD to SQLPLUS.
but its not looping through the all the folders ..

Reply
1. santhosh
  
  July 27, 2016
  
  Written below script based on your input for oracle.
  
  [CODE]
  @echo off
  setlocal enabledelayedexpansion
  set /p spath=%1Enter Script Path:
  set /p user=%2Enter Username:
  set /p pass=%3Enter Password:
  set /p Tnsname=%4Enter TNSName:
  
  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
  cd %spath%
  del MASTER.sql
  set cmd=’dir *.sql /b/s’
  type NUL > MASTER.sql
  echo WHENEVER SQLERROR EXIT SQL.SQLCODE >> MASTER.sql
  
  FOR /f %%G IN (%cmd%) DO (
  
  echo ******Concating %%G FILE to MASTER.sql ****** >> %logfilepath%
  copy /A MASTER.sql+”%%G” && echo. >> MASTER.sql
  )
  echo EXIT >> MASTER.sql
  
  echo ******** START OF MASTER FILE ******************** >> %logfilepath%
  type MASTER.sql >> %logfilepath%
  echo ******** END OF MASTER FILE ******************** >> %logfilepath%
  
  sqlplus %user%/%pass%@%Tnsname% @MASTER.sql
  
  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 %logfilepath% file for more details
  EXIT /b
  
  :Success
  echo ALL the scripts deployed successfully!!
  EXIT /b
  [/CODE]
  
  Reply
2. VidhyaSagar
  
  August 24, 2016
  
  Thanks Santhosh. I never tried in Oracle system
  
  Reply
Way Togo

September 12, 2016

Very nice! I verified in our test (not sure it was covered for sure) but the scripts wait for the previous script to be done before starting the next one. Very, very nice! Saved me much time and eliminated human error, always a good thing. Thanks!

Reply
Sandeep Suda

August 17, 2017

Hi Vidhysagar,

The script is pretty well and using the same scrip to deploy the stored procedures to our Release and Production servers.

One problem is placed all the *.sql files in the respective folders and executed the script. There are some errors encountered in the scripts while execution.

Now i make the changes to scripts where the errors has been encountered. Now how we can execute the scripts on the server after making the changes. shall we have to execute all the scripts again.

My requirement is to execute only error script/s encountered while first execution.

How we can achieve this. Please provide your suggestions on this.

Reply
Wayne

August 18, 2017

I have been using this script flawlessly on SQL 2008, Server 2008 but recently we had a vendor build a 2012 version of same application. I got better things to do than run 30 or 50 vendor scripts one at a time, again and again, TYVM!

However, it was failing on 2012 until I added the instance to the server name ‘MyServer\MyInstance’. Neither server is default instance, so not sure why it works on 2008, permissions, something is configured different on the server or user? Who knows, but just in case anyone else is having the issue, it stalls out at first script, then it times out and nothing has run, no spaces in directory path. If you run through command prompt you get a ‘Could not open a connection to SQL Server…’ error. Adding the instance after the server worked for me, it makes sense just curious why the 08 one didn’t need it? Oh well…

Vidhya, you have saved me and countless others time and aggravation with this, so elegant and simple (especially compared to other versions of this notion I have seen), and yet so effective! It is brilliant! Thank you very much for sharing this!

Reply
Akshay

March 28, 2018

Hi VidhyaSagar,
If we want to run multiple scripts and also we want it to run in a particular order then what can we do? Please suggest

Reply
1. VidhyaSagar
  
  April 8, 2018
  
  Hi Akshay, it should be handled in the file name. For example, you can prefix files as 01, 02 and so on.
  
  Reply
  1. Shailendra Sahu
    
    April 10, 2019
    
    Hi,
    Is there a way I can do it without renaming the folders?
    Its like I want to execute schema first, then tables, then SP etc. Something in this way? We have foldername like this but without renaming it to 1,2,3.
    
    Reply
    1. VidhyaSagar
      
      June 27, 2019
      
      Unfortunately no. In that case we should be using sqldeploy but that requires a dacpac content or a db project
      
      Reply
Shalini Singh

April 13, 2018

Hi VidhyaSagar,
I want to analyze files from multiple folders, as of now it’s fetching files from 1 folder but for me there are 4 folders and there are numerous files in those folder how can it be implemented using for loop.
Also in echo it’s printing the complete path directory for instance its printing
C:\folder\file.txt
but i want to fetch all the files but only
\folder\file.txt to be printed.

Can you provide some help on this? It’ll be really helpful

Reply
1. VidhyaSagar
  
  April 30, 2018
  
  Hi Shalini, I re-wrote the script using powershell, you can give a try that will help you out, any concerns on the new script please post in that comment section.
  http://sql-articles.com/scripts/powershell/batch-script-to-deploy-multiple-sql-files-version-2
  
  Reply
  1. Shalini Singh
    
    May 2, 2018
    
    Thankyou. It’s helpful
    
    Reply
SREEKANTHlingaladinne

September 28, 2018

how to deploy SQL files from a folder into oracle-DB using batch script while running the batch script if any PL/SQL error comes it should stop the script comes out from sqlplus.

Reply

Batch script to deploy multiple SQL files

Comments

44 responses to “Batch script to deploy multiple SQL files”

Leave a Reply Cancel reply