At one point in time I was tasked with running over 70 databases on more than a dozen servers without any of the costly third-party tools that exist to accomplish such tasks and I often get asked how can you handle such large numbers of databases. This is a small article that will show you how to use the handy OSQL command line utility provided with SQL Server to execute repetitive tasks against large numbers of databases or manage large numbers of servers. The OSQL command line utility allows you to execute Transact-SQL statements, system procedures, and script files against multiple servers and databases by using ODBC connections to those servers and databases. The syntax of the OSQL utility allows you to specify which server, database, login, password, input file, output file, as well as formatting functions you want to run with your SQL script. By creating a small bat file containing this information for the servers and database you manage and creating a SQL script file, you can easily perform repetitive SQL tasks with one or two mouse clicks.

OSQL Syntax

OSQL
    [-?] |
    [-L] |
    [
        {
            {-U login_id [-P password]}
            | -E
        }
        [-S server_name[\instance_name]] [-H wksta_name] [-d db_name]
        [-l time_out] [-t time_out] [-h headers]
        [-s col_separator] [-w column_width] [-a packet_size]
        [-e] [-I] [-D data_source_name]
        [-c cmd_end] [-q "query"] [-Q "query"]
        [-n] [-m error_level] [-r {0 | 1}]
        [-i input_file] [-o output_file] [-p]
        [-b] [-u] [-R] [-O]
    ]

OSQL Parameters

Putting It All Together

Using OSQL can be as made as fancy as you want it to be, but I find that the creation of two simple files will allow me to accomplish most the tasks I've needed to in a quick manner against numerous databases.

The first thing you need to do is to create your batch file or files based on the database groupings you want to run statement against. Using a simple text editor create the file with your parameters and save the file with a .bat extension. You can create this batch file to use a text file as the input file which will keep you from having to adjust parameters each time you want to run the batch process. A sample of what I do can be found in the following example:

OSQL -Usa -Ppassword -Sserver1 -dDatabase1 -n 
-iC:\OSQL_SCRIPTS\SQL_SCRIPTS\sqlscript.sql 
-oC:\OSQL_SCRIPTS\OUTPUT\OSQLoutput_db1.txt
OSQL -Usa -Ppassword -Sserver2 -dDatabase2 -n 
-iC:\OSQL_SCRIPTS\SQL_SCRIPTS\sqlscript.sql 
-oC:\OSQL_SCRIPTS\OUTPUT\OSQLoutput_db2.txt

What I usually do is create and test the SQL script in Query Analyzer and then save that script to a file named sqlscript.sql (I actually have several files with different names for different groupings of databases).

When you execute the .bat file, it will start up OSQL, connect to each defined server, execute a USE statement for the database defined, and execute the SQL command contained in the SQL script file creating a separate output file for each connection. Learning to format the output to a form that is readable takes some practice, but I usually use OSQL to find the location of the data I am interested in and then run the query in Query Analyzer so I can format the data in a quick and easy manner.

Summary

Taking the time to create OSQL bat files for groups of servers and/or databases and using these files to manage your environment for repetitive tasks will pay off as you find yourself only needing a few minutes to create a database object or run a query against dozens of databases located on dozens of servers. So keep asking for the expensive toys some think they need to mange multiple databases but until your company agrees to your request, save yourself a lot of time and frustration by figuring out how you can use OSQL to manage your environment.

Copyright 2002 by Randy Dyess, All rights Reserved

TransactSQL.Com