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

Views: 11.019
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 all DBA and most developers have had to execute several SQL scripts in a practical and fast way, not forgetting any and still logging operations for any queries. This activity is very common and today I had 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 would not 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

SQL Server - Batch processing by executing SQL Scripts in a folder directory

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

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.

SQL-Server-Batch-processing-executing-SQL-Scripts-in-a-folder-directory-SQLCMD_4

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.

Execution log example:
SQL Server - Batch processing executing SQL Scripts in a folder directory SQLCMD Advanced

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