How to run multiple SQL scripts

Scenario 1:-

Create a .bat file with the below code and store the .bat file in the folder containing all the .sql scripts.

setlocal
SET SQLCMD=sqlcmd -S<servername\instancename> -d<database name> -E
for %%d in (*.sql) do %SQLCMD% -i%%d
endlocal

Double click the .bat file and watch as all your .sql scripts in the folder getting executed on the server specified with the -S and Database by -d under windows authentication specified by -E. If you want to use sql authentication instead, then you can specify the user name with -U and password with -P parameters.

Scenario 2:-

To execute all the scripts on multiple servers or instances, append the same code to the .bat file specifying how many ever sql servers you would want to run the scripts onto.

eg:- runonmultpleservers.bat

setlocal
SET SQLCMD=sqlcmd -S<servername1\sql2008> -d<database name> -E
for %%d in (*.sql) do %SQLCMD% -i%%d
endlocal

setlocal
SET SQLCMD=sqlcmd -S<servername2\sql2008R2> -d<database name> -E
for %%d in (*.sql) do %SQLCMD% -i%%d
endlocal

setlocal
SET SQLCMD=sqlcmd -S<servername1\sql2008reports> -d<database name> -E
for %%d in (*.sql) do %SQLCMD% -i%%d
endlocal

Scenario 3:-

Often DBAs need to run a set of .sql scripts from a folder containing loads of .sql scripts, and want to be sure not to miss anything.

In this case you can create a .sql script (say final.sql) calling all the required scripts to be run and execute this via command prompt.

eg:- final.sql

/* SCRIPT: final.sql */
/* Run set of .sql scripts onto a database in particular order*/

— This is the main caller for each script
SET NOCOUNT ON
GO

PRINT ‘Running scripts’
GO

— uncomment the below line; If you need to Stop running further scripts on error
—:On Error exit

:r D:\sqlscripts\script1.sql

:r D:\sqlscripts\script2.sql

:r D:\sqlscripts\script10.sql

:r D:\sqlscripts\script4.sql

PRINT ‘Scripts executed ‘
GO

Finally execute this final.sql from command prompt as below:

C:> sqlcmd -E -S<servername\instance> -d<databasename> -iC:\final.sql

or

From SSMS query analyzer as below:

xp_cmdshell ‘sqlcmd -E -S<servername\instance> -d<databasename> -iC:\final.sql’

This entry was posted in SQL Server, T-SQL Scripts, Tricks and tagged , . Bookmark the permalink.

Leave a Reply