SQL tasks automation: apply scripts from folder

You will be amazed how common this question is:

I have a folder with arbitrary set of SQL scripts and want to apply them all automatically. How do I do that?

In other words:

  • Having a folder – ex: C:\AutoSQLScripts – with some unknown set of SQL scripts
  • And a SQL Instance where scripts need to be applied
  • Run a scheduled task to perform the operation automatically.

Solution:

First, lets figure out single operation – run SQL Script from command line in unattended mode using osql utility:

::
osql -S MYSERVER -d MYDATABASE -E -i MySqlScript.sql  -o report.txt
::

Where:

-S indicates server name
-d is an alternative to USE db_name inside the script
-E indicates that trusted connection would be used
-i identifies the file that contains a batch of SQL statements
-o identifies the file that receives output from osql

Simple so far…

Fun starts when we need to go through files in the folder. Using “magic” of Power Shell, it is also easy:

::
FOR /f "TOKENS=*" %%a IN ('dir /b "%1*.sql"') do ECHO %%a
::or
SET _PATH=%1
IF (NOT %_PATH%=="") SET _PATH = '-p "%_PATH%"'
FORFILES %_PATH% -s -m *.* -c "CMD /C ECHO @FILE"
::

Let’s ignore the end for now and concentrate on the syntax of the loop itself.

FOR /f indicates that we need to perform a loop against a set of files specified in IN. To produce the set we are using “raw” dir of the folder, where %1 is, if specified, an input param of the batch file call.

FORFILES is a little more complex and given here for comparison, yet produces the same result.

Note: commands above work only with “local” (drive: based) locations. If you need apply scripts from Network folder, map it first as a local drive then apply scripts.

Now let’s combine SQL call with the batch processing and we would get the following ApplyScript.cmd which could be used to apply any scripts from current or specific folder to specified SQL Server/Database instance:

::
@ECHO OFF
SET _SERVER=MYSERVER
SET _DB=MYDATABASE
FOR /f "TOKENS=*" %%a IN ('dir /b %1*.sql') DO CALL osql -S %_SERVER% -d %_DB% -E -i %%a  -o report.txt
::

Enjoy.

This entry was posted in Fun stuff with SQL Server, Technology and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

  • About

    From being a junior developer all the way to Development Manager position, I was always interested in new technologies. Passionate speaker, IT junky, developer, architect, team lead, and development manager - many hats, one goal - making software better and closer to people’s needs. For the most part I am using my blog as a scratch pad, writing small articles on things which I came across, was asked about more then once, and which would otherwise require additional research again and again.

    View Serguei Dosyukov's profile on LinkedIn
  • Testimonials

    I’m a huge fan of this theme. I’ve got more than 90,000 pictures in my gallery3 running on a Ubuntu...

    Jklobo

    I've been using this [theme] since the start. It is by far the most attractive, feature packed and stable of...

    Rhyull

    The greydragon theme is fantastic. It’s clean, stable and feature rich. It took me a while to decide to move...

    Ed

    more...

  • Categories