How to automate multiple script execution using SQLCMD and bath file.

How to automate multiple script execution using SQLCMD and bath file.

Problem

As a DBA’s many times we receive requests to execute multiple scripts, during some release times we end up having more than 100 scrips as well. Executing each script one at a time is always time consuming and chances of missing a script or two is high.

Solution

We can automate this by few simple steps:

1.      Create folder and place all scripts in the folder.

2.      Open notepad and add below script to and saves Execute_ALL_SQL.bat or whatever you feel comfortable.

@echo off

ECHO %USERNAME% started the batch process at %TIME%  >output.txt

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

echo %%f >>output.txt

sqlcmd.exe  -S -E -d yourDB -i "%%f" >>output.txt

    )

Pause

“ECHO %USERNAME% started the batch process at %TIME%  >output.txt” name will write name of the user who is executing script to output file.

Next line is For loop to loop through all .SQL files.

“echo %%f >>output.txt” while write SQL file name to output file.

Definition of “sqlcmd.exe  -S ServerName -E -d youDB -i "%%f" >>output.txt” SQLCMD.exe will help us run SQL commands. In this case

·        -S is SQL Server name on which you want to execute script.

·        -E is for windows or trusted authentication or we can use -U username and -P password for SQL Authentication.

·        -i input SQL files.

Added Pause at the last line just to let you know script execution completed and read out for any errors. Press space button it will close the window.

3.      Double click on the BAT file boom you are done with executing all scripts in one short.

4.      You can share the content of output file which will the script you have executed, how many rows effected if any.

Note: make sure both “BAT” file and “SQL” files are in same folder.