Creating the Build Scripts to Automate the Incremental Daily Database Builds:

I had the Requirement to run multiple scripts to create the Database, Stored Procedures, Functions, Triggers; etc.It was time consuming to run the scripts manually.
So I thought of using the Batch Files to automate the process.
Tools Used:
SQLCMD: This command line utility tool is shipped with SQL Server Database Engine and provides efficient integration with Batch files and SQL Server Databases.
Before running the scripts, I have to make sure the Database Exists.
If the database does not exist, I have to create the database with the Name passed as Parameter and then execute the T-SQL Scripts. I selected SQLCMD Tool to accomplish this task.
SYNTAX:
SQLCMD
-S Server Name
-d Database Name
-b Exit on Error in the Batch
-Q Run the Query in Database and close the Connection with the Database
-q Run the Query in Database and leave the Connection Open with the Server

SQLCMD Uses the OLEDB Interface to connect to the Server where as the
SQL Management Studio uses the SQLCLIENT to connect to the SQL Server.
It might cause some difference in the Query Execution.

Passing the variables to the command batch file:
In the Batch file all the variables are treated as strings. It’s different from any other procedural languages. So we do not have to define the variable data type.

Set DatabaseServer =%1
It means the first argument we pass in the command line would be the Database Server Name.
Set DatbaseName=%2 .
The second argument would be the Database Name. While passing the arguments in the command line, there has to be space between the arguments.

The final Build Script:
ECHO OFF
REM: -b On Error Batch Abort in sqlcmd
REM: >> or > this operator can be used in place of -o option to append the output.

set ServerName=%1
set DatabaseName=%2
set WorkDirectory =%3
set LogfileName =%4

echo The ServerName used in applying this Build: %1
echo The DatabaseName used in applying this Build:%2
echo The directory used in the Build: %3
echo The Logfile Used to log Errors in this Build: %4

echo: The Build Start time %date% %time%

ECHO: Checking the existence of database: %2 in server: %1
Echo: If not exists creating the Database: %2 in Server: %1


set Query="IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='%2')
BEGIN CREATE DATABASE %2 END"
sqlcmd -S %1 -d Master -Q %Query%

echo: Checking the connection to the Server: %1 and Database: %2
set qry ="set nocount on"
sqlcmd -S %1 -d %2 -Q "%qry%"
IF %Errorlevel% NEQ 0 goto FailureExit:

Echo Applying the Scripts at Server: %1 and Database %2

for %%B in (%3\*.sql) do (sqlcmd -S %1 -d %2 -b -i "%%B" >> %4)
IF %Errorlevel% NEQ 0 goto FailureExit:

REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:

REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:

Echo The Build successfully applied at Server: %1 and Database: %2
Echo The Build completed Time %date% %Time%

:FailureExit echo The build Cancelled at Server: %1 and Database: %2

Processing the SQL Files in the Subdirectory

The following batch file processes the .sql files in the Given Server Name, DatabaseName, and WorkingDirectory.The log file is passed as the fourth Parameter where the output of the command execution saved.

echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4

for /d %%B in (%3\*) do (
for %%C in ("%%B\*.sql") do sqlcmd -S %1 -d %2 -i %%C >>%4)

Iterating through the Files without the Double loop:

echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /r %3 %%B in (*.sql) do (sqlcmd -S %1 -d %2 -i %%B >>%4)

Using the /r command to search the .sql extension files in the Foldername passed as 3rd
parameter.