June 13st, 2016
11
Powershell / CMD Database Maintenance Query Development

SQL Server - How to Batch All .sql Scripts in a Folder or Directory by SQLCMD

Views: 20.662 views

In this article

Reading Time: 3 minutes

Hello people,
Good night!

In this post I will demonstrate how to run multiple batch scripts that are in a given folder, generating execution log, with just one line of code. This instruction is very simple, and allows a series of customizations to meet your need.

I believe that every DBA and a good part of the developers have already had to execute several SQL scripts in a practical and fast way, not forgetting any and still logging the operations for any questions. This activity is very common and today I needed to export some SP's and functions from one bank to another, generating 174 SQL scripts (I always create 1 per object). And of course I wouldn’t run them one by one manually ..

For this solution, I will use the Windows Command Prompt language (CMD.exe) and SQLCMD, the SQL Server Command Line Utility.

Remember that it is the responsibility of every DBA to evaluate and validate all scripts submitted to it, preferably in a testing, development and / or preproduction environment. Do not use the solution presented here to run multiple batch scripts directly in production without at least testing them in a test environment before.

Basic example

Create a .BAT file in your scripts directory (In this example, I will use the name “Run Scripts.bat”) with the following command:

for %%G in (*.sql) do sqlcmd /S localhost\sql2014 /d Testes -E -i"%%G" >> Execucao.log
pause

1 2	for %%G in (*.sql) do sqlcmd /S localhost\sql2014 /d Testes -E -i"%%G" >> Execucao.log pause

Where:

/ S: define server \ instance where scripts will be executed
/ d: define the database where scripts will be executed
-E: Specifies that the authentication mode will be Trusted Connection (Windows AD Authentication). If you want to use SQL authentication, use -User and -Password
-i: Specifies the input file that contains the commands that will be executed.

Note that in this solution, the log is generated with the result of the execution of each script, without date and time information and without separation between the scripts. With this, the results are not very organized within the file. If you don't care much about log output, you can use this solution quietly.

Advanced Example:

For those who like a little more organization in the execution logs, I will also provide a slightly more advanced solution, creating a very nice execution log. Also, I added the -e parameter (if you don't like it, you can remove it), which displays beyond the result of the executed scripts, the contents of the script itself, to further highlight the execution.

setlocal enableDelayedExpansion
@echo off
ECHO. > "Execucao.log"

for %%G in (*.sql) do (

	ECHO -------------------------------------------------------- >> "Execucao.log"
	ECHO !DATE! !TIME! Executando o script "%%G"... >> "Execucao.log"
	ECHO -------------------------------------------------------- >> "Execucao.log"
	ECHO. >> "Execucao.log"
	
	sqlcmd /S localhost\SQL2014 /d Testes -E -e -i"%%G" >> "Execucao.log"
	
	ECHO. >> "Execucao.log"
	ECHO Fim da execucao: !DATE! !TIME! >> "Execucao.log"
	ECHO -------------------------------------------------------- >> "Execucao.log"
	ECHO. >> "Execucao.log"
	ECHO. >> "Execucao.log"
	
)

PAUSE

setlocal enableDelayedExpansion

@echo off

ECHO. > "Execucao.log"

for %%G in (*.sql) do (

ECHO -------------------------------------------------------- >> "Execucao.log"

ECHO !DATE! !TIME! Executando o script "%%G"... >> "Execucao.log"

ECHO -------------------------------------------------------- >> "Execucao.log"

ECHO. >> "Execucao.log"

sqlcmd /S localhost\SQL2014 /d Testes -E -e -i"%%G" >> "Execucao.log"

ECHO. >> "Execucao.log"

ECHO Fim da execucao: !DATE! !TIME! >> "Execucao.log"

ECHO -------------------------------------------------------- >> "Execucao.log"

ECHO. >> "Execucao.log"

)

PAUSE

Execution log example:

Thanks for stopping by and see you next post!
Hug.

sql server batch script run multiple script files from a sqlcmd batch batch execute processing

sql server run batch script multiple script files from one sqlcmd batch run folder

Tags: sql server sql sqlcmd batch

Anderson Da Silva said:

26 July 2022 to 19: 20

Hello, how are you, I would like you to help me if you can.
I adapted this code below to run the script in the database, but I would like to make it ask when it gives an error if I want to continue, try again or exit

@ Echo off
chcp 1252
SET ds_Instance=.
SET ds_UseDatabase=cin2
SET ds_RootDir=%CD%

rem reset errorlevel to zero
see >null
class
echo. ———————————-
echo. Starting script execution
echo. ———————————-
echo.
echo.
for /R %%j in (*.sql) do (
echo == Running: %%j

SQLCMD -b -i “%%j” -S %ds_Instance% -E -d %ds_UseDatabase% -o “%ds_RootDir%\Result.txt”

if errorlevel 1 (
echo !!!ERROR!!!
echo ERROR: %%j >> “%ds_RootDir%\log_Scripts_Executados.txt”
echo ERROR: %%j >> “%ds_RootDir%\log_Scripts_Executados_Detalhe.txt”
if exist “%ds_RootDir%\Result.txt” copy “%ds_RootDir%\Result.txt” “%%j.error.txt”
else (
echo ok
echo OK: %%j >> “%ds_RootDir%\log_Scripts_Executados.txt”
echo OK: %%j >> “%ds_RootDir%\log_Scripts_Executados_Detalhe.txt”
)
echo.
if exist “%ds_RootDir%\Result.txt” type “%ds_RootDir%\Result.txt” >> “%ds_RootDir%\log_Scripts_Executados_Detalhe.txt”
echo. >> “%ds_DirRoot%\log_Scripts_Executados_Detalhe.txt”

)
del “%ds_RootDir%\Result.txt”
echo. ———————————-
echo. Concluded!
echo. ———————————-
echo.
echo.
echo.
Break

maybe you can help me

Reply
Acaciolr-DBA said:

23 from 2021 to 17 at 15: XNUMX

Great master, blz? I think a PowerShell version of this script would be cool. ?

Reply
fleshy said:

12 February 2020 to 21: 36

Perfect! I ran a 24 line command in a very short time! Congratulations!

Reply
Magno Rodolfo dos Santos said:

4 April 2018 09 gies: 51

Thank you very much, helped a lot here to automate script execution on the client. Good job, congratulations.

Reply
Marciano Silva said:

December 11 from 2017 to 10: 17

Good morning

How do I export only the return of the command?

Reply
Geovani Moura said:

6 April 2017 10 gies: 08

How do you return only the rows that had error?
My code is like this, but I just want to return what gave problem not all
for %% G in (* .sql) do (
ECHO ———————————————————
ECHO ———– Start of execution:! DATE! !TEAM! ———–
ECHO ———– Running the script “%% G”…
sqlcmd -S alphasfdb02 \ dese -i “%% G”
ECHO ———– End of execution:! DATE! !TEAM! ————–
ECHO ———————————————————
ECHO.
)

Reply
- Dirceu Resende said:
  
  6 April 2017 15 gies: 50
  
  Geovani,
  Good afternoon.
  
  I believe this is not possible because DOS Prompt (cmd) has no error handling and exception for SQL commands to identify whether or not the sqlcmd command has failed.
  
  However, you can modify sqlcmd output by using the -r0 parameter and write error messages and warnings to a separate file, as shown in this link: http://dba.stackexchange.com/questions/20096/how-to-get-sqlcmd-to-output-errors-and-warnings-only
  
  Any questions, I am available.
  
  Reply
Cassia Silva said:

25 from 2016 to 11 at 01: XNUMX

Hello I would like to know how to add in bat the order of execution of scripts.
Anyway already helped mto

Reply
- Dirceu Resende said:
  
  26 from 2016 to 07 at 18: XNUMX
  
  Cassia,
  Good morning and thanks for stopping by.
  
  Because it is a DOS script, what you can do to set the execution order is to use the sort by file name or date created / modified. In the case of this example, scripts will be executed sorting by filename. Usually when I have a predefined order of execution, I number the files. If it's just a few scripts, you can put some character in front of the name to make sure they are sorted first.
  
  Sorting by date is possible, but I don't think it's a good strategy. Anyway, I will post how to do this.
  
  Reply
Thiago Carvalho said:

20 June 2016 to 11: 55

Good morning!

Now I will stop running script by script when I have to update the structure of a database. 😀

Thanks for the solution.

Reply