Many times I worked for companies that do not have any tool for production deployment. None, zero, zilch. Yes, you could use Enterprise Manager to generate database objects in one file or multiple files, but both of these techniques have issues. With the first approach it's difficult to manage multiple changes to the script. With the second approach it's time consuming to run all the scripts because you need to open and run each of them.

It's also a major issue if it's not new development and you have to know and keep track of what was added and what was modified. And it's become complicated when you need to move into production just some changes. Using OSQL command and DOS you could lower the pain.

Step 1: Using OSQL command prompt utility with explicit arguments to create, alter database objects.

Identify the SQL Server name you are going to use for these study tasks. Run this statement: SELECT @@SERVERNAME. In our example, server name is Snowdrop, and we are going to run our scripts in Flowers database.

The OSQL command prompt utility allows you to execute Transact-SQL statements and scripts.

Here is an example of OSQL utility usage:

OSQL -S Snowdrop -d Flowers -E -i C:\SBE01\TST\TBL\ABC\SBE001_CRT1_Test1.SQL -o C:\SBE01\LOG\SBE001_CRT1_Test1.LOG -n

This OSQL command should be executed from the command prompt. (Click Start, click Run, Type in CMD, Click OK, paste your OSQL command, press Enter) It will run the script saved in the C:\SBE01\TST\TBL\ABC\SBE001_CRT1_Test1.SQL file and place the output message in file C:\SBE01\LOG\SBE001_CRT1_Test1.LOG.

Please keep the file(directory) name as short as possible because there is some limitation on the full path length that may cause issues while working with SSIS (DTS) packages.

You can check the result of execution from Query Analyzer or SQL Server Management Studio:

use Flowers
go
select * from information_schema.tables
where table_name = 'tblTest1'

By executing a script from command prompt we created a table. We do not need to open each script from a tool like Query Analyzer or SQL Server Management Studio in order to run it.

Try this experiment. Copy and paste the above OSQL command 20 times in a text file. Copy all 20 lines and paste them into command prompt. All script are executed except the last one. We need to press Enter key to run the last script. The conclusion is we can run all our scripts in one shot. We do not need to open and run one after another script to deploy all our database objects. We can do it in one shot. And this is what some DBAs do.

What if we could run this command multiple times dynamically changing the name of a server or a database or a script file name or a folder name? What if we could programmatically generate all OSQL command lines we need for each release (production or QA)? If we could do this automatically, on a fly, we would not need manually create all these command lines, we would not need to manually apply all changes to the command files every time we add or remove or rename a script to the folders during the development cycle.

Step 2: Combine the power of DOS and OSQL command prompt utility to reuse the same code multiple times.

Imagine we have a stored procedure. By passing different input parameters to our stored procedure

we can get desired results without rewriting our code. We are going to use a DOS command file

instead of stored procedure to execute OSQL utility with different input arguments.

This will allow us to change generic arguments, like server name, database name, script file.

We are going to rewrite the OSQL command, but this time this command will contain

declared variables, stored in a batch file C:\SBE\BATCH\SBE001_DTS1_OSQL.BAT.

By changing variables we have the ability to execute

different scripts using the same OSQL command template.

This is the content of the SBE001_DTS1_OSQL.BAT file:

SET DB_SRVR=%1
SET DB_NAME=%2
SET CRT_DIR=%3
SET LOG_DIR=%4
SET SCR_NAME=%5
SET SCR_EXT=%6
SET LOG_EXT=%7
OSQL -S %DB_SRVR% -d %DB_NAME% -E -i %CRT_DIR%\%SCR_NAME%%SCR_EXT% -o %LOG_DIR%\%SCR_NAME%%LOG_EXT% -n

Description of OSQL command arguments used:

-S server name

-d database name

-E Uses a trusted connection, no password required

-i names the input_file with a batch of SQL statements or stored procedures to be executed

-o output_file that receives output from OSQL.

-n removes numbering and the prompt symbol (>) from output lines.

Comment: The command arguments are case sensitive.

Description of DOS variables used:

%DB_SRVR% variable contains server name

%DB_NAME% variable contains database name

%CRT_DIR% variable contains the name of the folder with the source script file

%LOG_DIR% variable contains the name of the folder where OSQL utility will write output message

%SCR_NAME% variable contains the name of the script file to be executed

%SCR_EXT% variable contains the extension of the script file

%LOG_EXT% variable contains the extension of the log file

With this command:

SBE001_DTS1_OSQL.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABC C:\SBE01\LOG SBE001_CRT1_Test2 .SQL .LOG

We created table tblTest2.

Step 3: Practice.

If we need to run the same script on development, staging and production servers all we should do is to change the server name in the command that calls OSQL utility. To execute the same command on staging server, change the development server name - Snowdrop to staging server name - Snowflake and run this command:

OSQL -S Snowflake -d Flowers -E -i C:\SBE01\TST\TBL\ABC\SBE001_CRT1_Test1.SQL -o C:\SBE01\LOG\SBE001_CRT1_Test1.LOG -n

Step 4: Practice.

Using the SBE001_DTS1_OSQL.BAT file, execute the following command from command prompt, from current directory directory C:\SBE01\TST\BATCH!

SBE001_DTS1_OSQL.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABC C:\SBE01\LOG SBE001_CRT1_Test8 .SQL .LOG

Here you specify SBE001_DTS1_OSQL.BAT - executable(batch) file that contains the OSQL command with DOS variables. The variables are provided in the same order they are listed in the batch file. In other words they are positional variables.

By executing the OSQL command utility from command file we will create table tblTest8. If we want to create table tblTest9 from the same folder all we need to do is to change file name in the command line: SBE001_DTS1_OSQL.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABC C:\SBE01\LOG SBE001_CRT1_Test9 .SQL .LOG

By changing variables in the command line we can reuse our command file multiple times.

>Step 5: Practice.

Now, we want to execute all four scripts we have in C:\SBE01\TST\TBL\ABE folder: SBE001_CRT1_Test4.SQL, SBE001_CRT1_Test5.SQL, SBE001_CRT1_Test6.SQL, SBE001_CRT1_Test7.SQL.

Each of this script contains create table statement ( tables to be created tbleTest4,

tbleTest5, tbleTest6, tbleTest7). Here is the content of the SBE001_CRT1_OSQL_OA_ABE.BAT file

SET DB_SRVR=%1
SET DB_NAME=%2
SET CRT_DIR=%3
SET LOG_DIR=%4
SET SCR_EXT=%5
SET LOG_EXT=%6
osql -S %DB_SRVR% -d %DB_NAME% -E -i %CRT_DIR%\SBE001_CRT1_Test4%SCR_EXT% -o %LOG_DIR%\%SBE001_CRT1_Test4%%LOG_EXT% -n
osql -S %DB_SRVR% -d %DB_NAME% -E -i %CRT_DIR%\SBE001_CRT1_Test5%SCR_EXT% -o %LOG_DIR%\%SBE001_CRT1_Test5%%LOG_EXT% -n
osql -S %DB_SRVR% -d %DB_NAME% -E -i %CRT_DIR%\SBE001_CRT1_Test6%SCR_EXT% -o %LOG_DIR%\%SBE001_CRT1_Test6%%LOG_EXT% -n
osql -S %DB_SRVR% -d %DB_NAME% -E -i %CRT_DIR%\SBE001_CRT1_Test7%SCR_EXT% -o %LOG_DIR%\%SBE001_CRT1_Test7%%LOG_EXT% -n

In this batch file we have explicitly written the script names to be executed.

This is the command line to be executed from command prompt.

SBE001_CRT1_OSQL_OA_ABE.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABE C:\SBE01\LOG .SQL .LOG

From Query Analyzer or SQL Server Management Studio you can check that all tables were created:

select * from information_schema.tables
where table_name in('tblTest4', 'tblTest5', 'tblTest6', 'tblTest7')
Comment: If we can programmatically create the batch file that contains the names of all scripts
 we have in the folder, we can now automatically create
 all database objects by running one command.
Step 6: Practice.
It's time to create all database objects we have in all folders under C:\SBE01\TBL. Here is the contents of SBE001_CRT1_OSQL_OT_TBL.BAT file.
CALL SBE001_CRT1_OSQL_OA_ABC.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABC  C:\SBE01\LOG .SQL .LOG
CALL SBE001_CRT1_OSQL_OA_ABD.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABD  C:\SBE01\LOG .SQL .LOG
CALL SBE001_CRT1_OSQL_OA_ABE.BAT Snowdrop Flowers C:\SBE01\TST\TBL\ABE  C:\SBE01\LOG .SQL .LOG
This file contains requests to execute batch files: SBE001_CRT1_OSQL_OA_ABC.BAT, SBE001_CRT1_OSQL_OA_ABD.BAT, SBE001_CRT1_OSQL_OA_ABE.BAT. We already reviewed SBE001_CRT1_OSQL_OA_ABE.BAT, the other files are similar.
The SBE001_CRT1_OSQL_OT_TBL.BAT command file will execute all scripts located under DDL directory. Again, by changing parameters we can control in what environment to execute all these scripts.
Run SBE001_CRT1_OSQL_OT_TBL.BAT file from current directory directory C:\SBE01\TST\BATCH. (To run SBE001_CRT1_OSQL_OT_TBL.BAT file - copy and paste the file name to command prompt line). Check that all tables were created from Query Analyzer.
select * from information_schema.tables
where 
    (Table_Name like 'tblTest%')
and Table_Type in ('BASE TABLE')
order by 3
You should have nine tables.
Please run file SBE001_CRV1_OSQL_OT_VIEW.BAT to create all views in folders ABC, ABD, ABE under
folder VIEW. Check that all views were created from Query Analyzer or SQL Server Management Studio.
select * from information_schema.tables
where 
    (Table_Name like 'vw_Test%')
and Table_Type in ('VIEW')
order by 3
You should have nine views.
Please run file SBE001_DML1_OSQL_OT_DML.BAT to populate some of the created tables.
The DML(data manipulation language) scripts are located in folders ABC, ABD, ABE under
folder DML. Check that all tables were populated.
select * from tblTest1
select * from tblTest2
select * from tblTest3
select * from tblTest5
select * from tblTest7
select * from tblTest9
Now we can run all scripts we have under folder TST using the command file SBE001_CRO1_OSQL_TST.BAT. Here are the contents of the file:
CALL SBE001_CRT1_OSQL_OT_TBL.BAT
CALL SBE001_CRV1_OSQL_OT_VIEW.BAT
CALL SBE001_DML1_OSQL_OT_DML.BAT
Summary
We can create all database objects without opening our scripts in Query Analyzer or SQL Server Management Studio. We can create all database objects by submitting ONE command file and we can control the sequence of execution of all the scripts with a series of command files.
If we could automatically create all these command files, we could automate the entire process of creating (altering, deleting) all database objects, and we could automate the deployment processes.
I used to use PERL for all command files. On one of my assignments I was told that according to company standards I can not use PERL. I was very upset. But I remember the saying my teacher always said to me: "Use everything you have for free and do not look for anything else". DOS - this is "something" that I found is free and is not forbidden.
References
"Version Control - Part 3 - Migrating Objects to Production" by Steve Jones
"The Waite Group's tricks of the MS-Dos Masters", Second Edition
by Waite Group (Corporate Author), John Angermeyer
"OSQL Utility By Don Schlichting"
"Manage Multiple SQL Server Installations and Databases with OSQL"by Randy Dyess
"SQL Server 2000 Books Online"
Credits:
my wife, my kids, my teacher Ilya Petrenko
my coworkers from Viatech Inc.
sqlservercentral Forums