AlterAlter sql scripts.
DataData sql scripts.
DataTypesData type sql scripts.
DBScriptsCreate database sql scripts.
ForeignKeysForeign keys sql scripts.
FunctionsFunction sql scripts.
IndexesIndex sql scripts.
PartitionFunctionsPartition function sql scripts.
PartitionSchemesPartition scheme sql scripts.
SchemasSchema sql scripts.
SQLJobsSQL job sql scripts.
stbDDLDefinition table sql scripts.
StoredProceduresStored procedure sql scripts.
tbDDLTransaction table sql scripts.
tbDDLPartitionPartition table sql scripts.
TriggersTrigger sql scripts.
ViewsView sql scripts.

DBScripts

CreateDatabaseFPM_LocalHost.sqlLocalhost Database
CreateDatabaseFPM_DevDB.sqlDevelopment Database
CreateDatabaseFPM_QADB.sql QA Database
CreateDatabaseFPM_ProductionDB.sql Production Database

BuildScripts (Parent)

Create_DB_Local.cmd
Create_DB_Dev.cmd
Create_DB_QA.cmd
Create_DB_Production.cmd
CreateDatabase_MyDatabase01_Local.cmd
CreateDatabase_MyDatabase01_Dev.cmd
CreateDatabase_MyDatabase01_QA.cmd
CreateDatabase_MyDatabase01_Production.cmd

Create_DB_local.cmd

Create actual database once the object database files has been generated.

@ECHO OFF

IF "%1" EQU "Quiet" GOTO RESTORE

SET /P userInput=This action will drop and recreate the MyDatabase01 database, do you wish to continue? (Y/N): 

IF "%userInput%"=="Y" GOTO RESTORE
IF "%userInput%"=="y" GOTO RESTORE

GOTO END

:RESTORE
ECHO.
ECHO Create_DB_Local.cmd
ECHO.

MKDIR C:\MSSQL 1>NUL 2>&1
MKDIR C:\MSSQL\MyDatabase01 1>NUL 2>&1
MKDIR C:\MSSQL\MyDatabase01\Data 1>NUL 2>&1
MKDIR C:\MSSQL\MyDatabase01\Log 1>NUL 2>&1


ECHO Folders C:\MSSQL\MyDatabase01

DEL *.SQL 1>NUL 2>&1
DEL *.LOG 1>NUL 2>&1

CALL CreateDatabase_MyDatabase01_Local.cmd 1>NUL 2>&1


ECHO.
ECHO -------------------------------------------------------------------------------
ECHO Building MyDatabase01 Database
ECHO -------------------------------------------------------------------------------
ECHO.
ECHO Creating MyDatabase01 Database...
cmd /c sqlcmd -b -i MyDatabase01_CreateDB.sql -S localhost -d master >CreateDB_IFX_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 partition file groups...
REM cmd /c sqlcmd -b -i MyDatabase01_AddFileGroups.sql -S localhost -d master >CreateFileGroups_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Partition Functions...
REM cmd /c sqlcmd -b -i MyDatabase01_CreatePartitionFunctions.sql -S localhost -d MyDatabase01 >CreatePartitionFunctions_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Partition Schemes...
REM cmd /c sqlcmd -b -i MyDatabase01_CreatePartitionSchemes.sql -S localhost -d MyDatabase01 >CreatePartitionSchemes_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Partition Tables...
REM cmd /c sqlcmd -b -i MyDatabase01_CreatePartitionTables.sql -S localhost -d MyDatabase01 >CreatePartitionTables_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Schemas...
REM cmd /c sqlcmd -b -i MyDatabase01_CreateSchemas.sql -S localhost -d MyDatabase01 >CreateSchemas_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

ECHO Creating IFX_MyDatabase01 Static Tables...
cmd /c sqlcmd -b -i MyDatabase01_CreateStaticTables.sql -S localhost -d MyDatabase01 >CreateStaticTables_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

ECHO Creating IFX_MyDatabase01 Transaction Tables...
cmd /c sqlcmd -b -i MyDatabase01_CreateTransTables.sql -S localhost -d MyDatabase01 >CreateTransTables_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

ECHO Creating IFX_MyDatabase01 Custom Data types...
cmd /c sqlcmd -b -i MyDatabase01_CreateDataTypes.sql -S localhost -d MyDatabase01 >CreateDataTypes_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Triggers Tables...
REM cmd /c sqlcmd -b -i MyDatabase01_CreateTriggers.sql -S localhost -d MyDatabase01 >CreateTriggers_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Functions...
REM cmd /c sqlcmd -b -i MyDatabase01_CreateFunctions.sql -S localhost -d MyDatabase01 >CreateFunctions_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO Creating IFX_MyDatabase01 Views...
REM cmd /c sqlcmd -b -i MyDatabase01_CreateViews.sql -S localhost -d MyDatabase01 >CreateViews_MyDatabase01.log
REM IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

ECHO Creating IFX_MyDatabase01 Base Data...
cmd /c sqlcmd -b -i MyDatabase01_InsertBaseData.sql -S localhost -d MyDatabase01 >InsertBaseData_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

ECHO Creating IFX_MyDatabase01 Foreign Keys...
cmd /c sqlcmd -b -i MyDatabase01_CreateForeignKeys.sql -S localhost -d MyDatabase01 >CreateForeignKeys_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

ECHO Creating IFX_MyDatabase01 Indexes...
cmd /c sqlcmd -b -i MyDatabase01_CreateIndexes.sql -S localhost -d MyDatabase01 >CreateIndexes_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR

REM ECHO.
REM ECHO Creating Stored Procedures for MyDatabase01 Database
REM ECHO.
ECHO Creating IFX_MyDatabase01 Stored Procedures...
cmd /c sqlcmd -b -i MyDatabase01_CreateStoredProcedures.sql -S localhost -d MyDatabase01 > CreateStoredProcedures_MyDatabase01.log
IF [%ERRORLEVEL%] NEQ [0] GOTO ERROR


REM --------------- END OF MyDatabase01 CREATION

GOTO SUCCESS

:ERROR
set exitstatus=1
ECHO -------------------------------------------------------------------------------
ECHO !!!!!                ERROR OCCURRED WHILE BUILDING DATABASE               !!!!!
ECHO -------------------------------------------------------------------------------
IF "%1" EQU "Quiet" GOTO END
PAUSE
GOTO END

:SUCCESS
set exitstatus=0
ECHO Database built successfully

IF "%1" EQU "Quiet" GOTO END
PAUSE
GOTO END

:END
ECHO %date% %time%
exit /b %exitstatus%

CreateDatabase_MyDatabase02_Local.cmd

Takes all files in a specific database object folder and generate one sql file for each database object.

REM @ECHO ON

DEL *.sql 1>NUL 2>&1

rem --------------------- MyDatabase01

TYPE ..\MyDatabase01\DBScripts\CreateDatabaseMyDatabase01_Local.sql > MyDatabase01_CreateDB.sql
REM TYPE ..\MyDatabase01\DBScripts\AddFileGroupsMyDatabase01_Local.sql > MyDatabase01_AddFileGroupsMyDatabase01_Local.sql
REM TYPE ..\MyDatabase01\PartitionFunctions\*.sql > MyDatabase01_CreatePartitionFunctions.sql
REM TYPE ..\MyDatabase01\PartitionSchemes\*.sql > MyDatabase01_CreatePartitionSchemes.sql

REM TYPE ..\MyDatabase01\Schemas\*.sql > MyDatabase01_CreateSchemas.sql
TYPE ..\MyDatabase01\stbDDL\*.sql > MyDatabase01_CreateStaticTables.sql
REM TYPE ..\MyDatabase01\tbDDLPartition\*.sql > MyDatabase01_CreatePartitionTables.sql
TYPE ..\MyDatabase01\tbDDL\*.sql > MyDatabase01_CreateTransTables.sql
TYPE ..\MyDatabase01\DataTypes\*.sql > MyDatabase01_CreateDataTypes.sql
REM TYPE ..\MyDatabase01\Triggers\*.sql > MyDatabase01_CreateTriggers.sql
REM TYPE ..\MyDatabase01\Views\*.sql > MyDatabase01_CreateViews.sql
TYPE ..\MyDatabase01\Data\*.sql > MyDatabase01_InsertBaseData.sql
TYPE ..\MyDatabase01\ForeignKeys\*.sql > MyDatabase01_CreateForeignKeys.sql
TYPE ..\MyDatabase01\Indexes\*.sql > MyDatabase01_CreateIndexes.sql


TYPE ..\MyDatabase01\StoredProcedures\*.sql > MyDatabase01_CreateStoredProcedures.sql

REM TYPE ..\MyDatabase01\Functions\*.sql > MyDatabase01_CreateFunctions.sql
Last modified: December 13, 2019

Author

Comments

Write a Reply or Comment