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

in

by

Comments

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

  1. Ramesh avatar
    Ramesh

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

    1. VidhyaSagar avatar

      Do you want a batch file to script out all the tables in the database using bcp?

    2. VidhyaSagar avatar

      @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

  2. akash singh avatar
    akash singh

    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.

    1. VidhyaSagar avatar

      @Akash – Use double quotes for the path or use 8 character folder name (you can obtain by executing dir C: /x)

  3. Gary avatar
    Gary

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

    1. VidhyaSagar avatar

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

  4. Nandhini avatar
    Nandhini

    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.

    1. VidhyaSagar avatar

      @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

  5. Bunny avatar
    Bunny

    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.

    1. VidhyaSagar avatar

      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.

  6. kittu avatar
    kittu

    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

    1. VidhyaSagar avatar

      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

  7. senthilkumar avatar
    senthilkumar

    Thanks Vidhi,

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

    1. VidhyaSagar avatar

      That’s good to hear

  8. Abhijeet Kamble avatar
    Abhijeet Kamble

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

    1. VidhyaSagar avatar

      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.

  9. Arjith avatar
    Arjith

    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?

    1. VidhyaSagar avatar

      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

  10. Nandhini avatar
    Nandhini

    @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

  11. Brijmohan avatar
    Brijmohan

    Hi VIDHYASAGAR,

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

    Thanks in Advance

    1. VidhyaSagar avatar

      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.

  12. Abdul Kalam avatar
    Abdul Kalam

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

    1. VidhyaSagar avatar

      @Abdul – As of now I dont have powershell version.. I’ll try to write it

      1. Abdul Kalam avatar
        Abdul Kalam

        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

        1. DKing avatar
          DKing

          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%

  13. Deepak avatar
    Deepak

    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.

  14. VidhyaSagar avatar

    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?

    1. ivan avatar
      ivan

      hi the code is not picking up the next file if there is also no space

      1. VidhyaSagar avatar

        I wrote another set of scripts similar to this using PowerShell. I’ll write a new article later and refer to it.

  15. santhosh avatar
    santhosh

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

    1. santhosh avatar
      santhosh

      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]

    2. VidhyaSagar avatar

      Thanks Santhosh. I never tried in Oracle system

  16. Way Togo avatar
    Way Togo

    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!

  17. Sandeep Suda avatar

    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.

  18. Wayne avatar
    Wayne

    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!

  19. Akshay avatar
    Akshay

    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

    1. VidhyaSagar avatar

      Hi Akshay, it should be handled in the file name. For example, you can prefix files as 01, 02 and so on.

      1. Shailendra Sahu avatar
        Shailendra Sahu

        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.

        1. VidhyaSagar avatar

          Unfortunately no. In that case we should be using sqldeploy but that requires a dacpac content or a db project

  20. Shalini Singh avatar
    Shalini Singh

    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

    1. VidhyaSagar avatar

      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

      1. Shalini Singh avatar
        Shalini Singh

        Thankyou. It’s helpful

  21. SREEKANTHlingaladinne avatar
    SREEKANTHlingaladinne

    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.

Leave a Reply to senthilkumar Cancel reply

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