SQL Server 2008 automated database drop, create and fill

Question

For the database in my project I have a drop/create script for the database, a script for creating tables and SPs and an Access 2003 .mdb file with some exported values.

To set up the database from scratch I can use my SQL management studio to first run one script, then the other and lastly manually run the sort of tedious import task.

But I would like to do this as automated as possible. Hopefully something like putting the three files in a folder along with a fourth script to execute. Looking something like:

run script "dropcreate.sql"
run script "createtables.sql"
import "values.mdb"

How is this done? I hope to avoid using SSIS and the like. The tricky this is of course the import of data, where I can't seem to find a simple way. It is also important that the files a left as they are and not embedded into anything.

Answer 1

Look at the bulk insert statment. Or Openrowset.

Answer 2

You can run SQL Server Management Studio in SQLCMD mode. In there you can run scripts as follows

:r c:\temp\DropCreate.SQL :r c:\temp\CreateTables.SQL

Alternately, you can run the whole thing from a batch file using SQLCMD.exe commands.

SQLCMD -S "." -E -i "c:\temp\DropCreate.SQL" SQLCMD -S "." -E -i "c:\temp\CreateTables.SQL"

Do you have an alternative to SSIS that can import the data for you? Usually to do any kind of transformations and loading you need error handling, lookups, etc that you will have to code yourself unless you use an off-the-shelf product.

You can read a lot about SSIS right here on SO

We have a similar project (create db, load data, create code). We do all of this inside a Database Project - with Visual Studio Team System Edition 2008 and GDR2.

Answer 3

:: DOC AT THE END 

@ECHO OFF
::BOOM BOOM BOOM CHANGE THIS ONE WHEN YOU ARE INSTALLAING DIFFERENT DATABASE
SET DbName=CAS_DEV


ECHO CREATE FIRST BACKUP OF ALL DATABASES ON THE DEFAULT INSTANCE ONES: 
ECHO CREATING THE LOG FILES

echo THIS IS THE ERROR LOG OF THE UPDATE OF THE %DbName% ON %DATE% >error.log
echo THIS IS THE INSTALL LOG OF THE UPDATE OF THE %DbName% ON %DATE% >install.log


ECHO STARTTING BACKUP 
CD .\0.BackUp
ECHO FOR EACH SQL FILE DO RUN IT THIS WILL TAKE A WHILE 
ECHO SINCE WE ARE GOING TO MAKE A BACKUP FOR ALL THE DATABASES ON THE CURRENT HOST 

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d MASTER  -t 30000 -w 80 -u -p 1 -b -i %%i  -r1 1>> "..\install.log" 2>> "..\error.log" 


ECHO GO ONE FOLDER UP 

ECHO SLEEP FOR 1 SECOND 
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH BACKUP GOING UP 
cd ..

ECHO THE BACKUPS ARE IN THE FOLDER
ECHO D:\DATA\BACKUPS
ECHO CLICK A KEY TO CONTINUE
ECHO ========================================================================================================================
PAUSE




ECHO STARTING INSTALLING FUNCTIONS
CD ".\1.Functions"
ECHO FOR EACH SQL FILE DO RUN IT 
ping -n 1 127.0.0.1 >NUL

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log" 

ECHO DONE WITH STORED PROCEDDURES GOING UP 
cd .. 
ping -n 1 127.0.0.1 >NUL 

ECHO HIT A KEY AFTER PAUSE 
PAUSE




ECHO START TO EXECUTE THE MIXED FILES
CD .\1.Mixed
ECHO CREATING THE LOG FILES
echo. >>"..\error.log"
echo. >>"..\install.log"
ECHO FOR EACH SQL FILE DO RUN IT 

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i %%i  -r1 1>> "..\install.log" 2>> "..\error.log" 

ECHO GO ONE FOLDER UP 
cd ..

ECHO SLEEP FOR 1 SECOND 
ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH MIXED GOING UP 

ECHO HIT A KEY AFTER PAUSE 
PAUSE
ECHO STARTING INSTALLING TABLES 
CD .\2.Tables
ECHO FOR EACH SQL FILE DO RUN IT 
ping -n 1 127.0.0.1 >NUL

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log" 


ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH TAbles GOING UP 
cd .. 
ping -n 1 127.0.0.1 >NUL 

ECHO HIT A KEY AFTER PAUSE 
PAUSE

ECHO STARTING INSTALLING Views
CD ".\3.Views"
ECHO FOR EACH SQL FILE DO RUN IT 
ping -n 1 127.0.0.1 >NUL

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log" 

ECHO DONE WITH Views GOING UP 
cd .. 
ping -n 1 127.0.0.1 >NUL 

ECHO HIT A KEY AFTER PAUSE 
PAUSE



ECHO STARTING INSTALLING stored procedures
CD ".\5.StoredProcedures"
ECHO FOR EACH SQL FILE DO RUN IT 
ping -n 1 127.0.0.1 >NUL

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i" -r1 1>> "..\install.log" 2>> "..\error.log" 

ECHO DONE WITH STORED PROCEDDURES GOING UP 
cd .. 
ping -n 1 127.0.0.1 >NUL 

ECHO HIT A KEY AFTER PAUSE 
PAUSE

ECHO STARTING INSTALLING Triggers
CD ".\6.Triggers"
ECHO FOR EACH SQL FILE DO RUN IT 
ping -n 1 127.0.0.1 >NUL

for /f %%i  in ('dir *.SQL /s /b /o') DO ECHO   %DATE% --- %TIME% RUNNING  %%i 1>>"..\install.log"&SQLCMD -U ysg -P pass -H hostname -d %DbName% -t 3000 -w 80 -u -p 1 -b -i "%%i"  -r1 1>> "..\install.log" 2>> "..\error.log" 

ping -n 1 127.0.0.1 >NUL
ECHO DONE WITH Triggers GOING UP 
cd .. 

ping -n 1 127.0.0.1 >NUL 

ECHO HIT A KEY AFTER PAUSE 
PAUSE


ECHO Please , Review the log files and sent them back to Advanced Application Support 


set mailadd= yordan.georgiev^@oxit.fi

:: WE USE THE "%cd%\bin\bmail.exe".EXE UTILITY TO SEND OURSELF AN E-MAIL CONTAINING THE TEXT FILE 
:: ALTERNATIVE SMTP MIGHT BE company.com, UNCOMMENT THE NEXT LINE FOR ALTERN
::cmd /c "%cd%\bin\bmail.exe" -s company.com -m %computername%.txt -t %mailadd% -a %computername% -h 
::"%cd%\bin\bmail.exe" -s smtp.company.com -m install.log -t [email protected] -a "POC 1.2 install log" -h

::"%cd%\bin\bmail.exe" -s smtp.company.com -m error.log -t [email protected] -a "POC 1.2 error log" -h





cmd /c start /max INSTALL.LOG
CMD /C start /MAX ERROR.LOG
echo DONE !!!
ECHO HIT A KEY TO EXIT 
PAUSE


:: WE GO TROUGH ALL THE FOLDERS AND RUN THE SQL FILES IN ALPHABETIC ORDER