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.

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.

31 thoughts on “Batch script to deploy multiple SQL files”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Reply

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

*