Database Folders
| Alter | Alter sql scripts. |
| Data | Data sql scripts. |
| DataTypes | Data type sql scripts. |
| DBScripts | Create database sql scripts. |
| ForeignKeys | Foreign keys sql scripts. |
| Functions | Function sql scripts. |
| Indexes | Index sql scripts. |
| PartitionFunctions | Partition function sql scripts. |
| PartitionSchemes | Partition scheme sql scripts. |
| Schemas | Schema sql scripts. |
| SQLJobs | SQL job sql scripts. |
| stbDDL | Definition table sql scripts. |
| StoredProcedures | Stored procedure sql scripts. |
| tbDDL | Transaction table sql scripts. |
| tbDDLPartition | Partition table sql scripts. |
| Triggers | Trigger sql scripts. |
| Views | View sql scripts. |
DBScripts
| CreateDatabase_DB01_LocalDB.sql | SQL Script, Localhost Database |
| CreateDatabase _DB01_DevDB.sql | SQL Script, Development Database |
| CreateDatabase_DB01_QADB.sql | SQL Script, QA Database |
| CreateDatabase_DB01_ProductionDB.sql | SQL Script, Production Database |
BuildScripts
| Create_DB_Local.cmd | CMD Script |
| Create_DB_Dev.cmd | |
| Create_DB_QA.cmd | |
| Create_DB_Production.cmd | |
| CreateDatabase_MyDatabase01_Local.cmd | CMD Script |
| 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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
TYPE ..\MyDatabase01\DBScripts\AddFileGroupsMyDatabase01_Local.sql > MyDatabase01_AddFileGroupsMyDatabase01_Local.sql
TYPE ..\MyDatabase01\PartitionFunctions\*.sql > MyDatabase01_CreatePartitionFunctions.sql
TYPE ..\MyDatabase01\PartitionSchemes\*.sql > MyDatabase01_CreatePartitionSchemes.sql
TYPE ..\MyDatabase01\Schemas\*.sql > MyDatabase01_CreateSchemas.sql
TYPE ..\MyDatabase01\stbDDL\*.sql > MyDatabase01_CreateStaticTables.sql
TYPE ..\MyDatabase01\tbDDLPartition\*.sql > MyDatabase01_CreatePartitionTables.sql
TYPE ..\MyDatabase01\tbDDL\*.sql > MyDatabase01_CreateTransTables.sql
TYPE ..\MyDatabase01\DataTypes\*.sql > MyDatabase01_CreateDataTypes.sql
TYPE ..\MyDatabase01\Triggers\*.sql > MyDatabase01_CreateTriggers.sql
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
TYPE ..\MyDatabase01\Functions\*.sql > MyDatabase01_CreateFunctions.sqlNote: Order is very important.
- Create Database
- Add File Groups
- Create Partition Functions
- Create Partition Schemes
- Create Schemas
- Create Static Tables
- Create Transaction Tables
- Create Triggers
- Create Views
- Insert Base Data
- Create Foreign Keys
- Create Indexes
- Create Stored Procedures
- Create Functions
Comments