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
@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.
VidhyaSagar, Could you please provide the batch scripts to extract table data using bcp out into multiple files dynamically from a database
Do you want a batch file to script out all the tables in the database using bcp?
@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 offsetlocal 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
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.
@Akash – Use double quotes for the path or use 8 character folder name (you can obtain by executing dir C: /x)
it didn’t work for me the script gets stuck at the first table. Any ideas???
@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.
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.
@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
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.
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.
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
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
Thanks Vidhi,
Your “Batch script to deploy multiple SQL files” is very useful for me..
That’s good to hear
is there a way to call multiple scripts(.sql) without having to type exit through automation?
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.
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?
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
@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
Hi VIDHYASAGAR,
When I execute this script it’s raise an error “Sqlcmd: ‘2’: Invalid filename.” , would you please help me.
Thanks in Advance
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.
Thanks VidhyaSagar , you saved my day . If possible can you post same implementation using PowerShell.
@Abdul – As of now I dont have powershell version.. I’ll try to write it
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
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%
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.
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?
hi the code is not picking up the next file if there is also no space
I wrote another set of scripts similar to this using PowerShell. I’ll write a new article later and refer to it.
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 ..
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]
Thanks Santhosh. I never tried in Oracle system
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!
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.
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!
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
Hi Akshay, it should be handled in the file name. For example, you can prefix files as 01, 02 and so on.
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
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
Thankyou. It’s helpful