The Automatic DBA

You know, there is nothing like a painfully long set of SQL scripts that need to be applied to multiple databases to get the creative juices flowing. As I stared at a list of 29 scripts that needed to be applied to at least 2 different databases (it could be much worse), I thought to myself, “There has to be a better way”. SQL Server Management Studio (SSMS) is a great tool, but I do hate how it makes you login to the database with each script you open. What is even worse, when I tried to open all 29 scripts at once it somehow spawned 7 different instances of SSMS in no apparent logical manner. Time to do some research.

I have used SQLCMD in the past, and I knew this would be the source of my enlightenment. SQLCMD is the 2005 command line successor to OSQL for SQL Server 2000. It allows you to execute SQL scripts at the command line. Nice.

Without any further banter, I’ll explain how you too can shave potential hours off of your DBA tasks. Note, this is a SQL Server specific tactic. I’m sure there is a way to do it in Oracle as well, but you’ll have to find some other chum to feed you that.

  1. Pick a folder to consolidate your .sql scripts (ie C:\AutoDBA)
  2. Create a text file called RunDBScripts.bat with the following contents
    DEL %1.%2.log
    REM == LOOP THROUGH ALL SCRIPTS AND RUN THEM, APPENDING TO LOG FILE
    FOR %%f in (*.sql) DO sqlcmd -S %1 -d %2 -i %%f >> %1.%2.log
    pause
  3. Create a second file called RunDBScripts.SERVER.DATABASE.bat (replacing SERVER and DATABASE with your specifics)
    RunDBScripts.bat SERVER DATABASE
  4. Execute the file created in step 3

That’s it, seriously. Try it out and you’ll be amazed and the time it can save you! You’ll also have a nice log file with the output results of the scripts.

The work is really done in the script created in step 2.  You loop through all files based on the *.sql wildcard.  For each file, it will issue the SQLCMD passing in the server, database, and file name.  The >> pipes the output to a log file.  Windows Authentication will be used by default, but you can also modify this to specify a username and password.

If anyone has any schnazzy additions to run from a UNC path or add a timestamp to the logfile name those would be worthwhile additions.

Thanks to these links for help in getting to this solution:

http://vadivel.blogspot.com/2007/11/sqlcmd-part-ix-batch-files.html
http://www.cs.ntu.edu.au/homepages/bea/home/subjects/ith305/description.html
http://technet.microsoft.com/en-us/library/ms180944.aspx

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: