Packaging Multiple Databases

pasha

I don't think Packager is able to process multiple DB's at once (would be a great feature!); however, I created a command line script that would allow us to package and deploy in one step. You only have to substitute the <DBADMIN> and <PASSWORD> with the applicable values. You also have to add path to the packager to the environmental variables. The proper use of this script would be as follows:

Process.bat <Source Server> <Destination Server> <DBName or All> <Port # of Source Server> <Port # of Destination Server>

---

@ECHO OFF

set List=%TEMP%\PACKAGE\*.exe

:CHECK_DIR
IF NOT EXIST %TEMP%\PACKAGE GOTO CREATE_DIR
IF EXIST %TEMP%\PACKAGE GOTO DLTFILES


:CHECK_VARIABLES

IF "%4"== "" (
SET SPORT=1433
) ELSE (
SET SPORT=%4
)
IF "%5"== "" (
SET DPORT=1433
) ELSE (
SET DPORT=%5
)
IF "%1"=="%2" GOTO SOURCE
IF "%1"=="" GOTO USAGE
IF "%2"=="" GOTO USAGE
IF "%3"=="" GOTO USAGE
IF "%3"=="All" (
GOTO ALLDB
) ELSE (
GOTO SPECIFIC
)



:ALLDB
sqlcmd -S %1,%SPORT% -U <DBADMIN> -P <PASSWORD> -Q "EXEC sp_msforeachdb @command1 = N'if ''?'' in (''master'',''model'',''msdb'',''tempdb'') return PRINT N''SQLPACKAGEr.exe /s1:%1,%SPORT% /u1:<DBADMIN> /p1:<PASSWORD> /db1:? /n:? /exe /q /excludedata:Table /recoverymodel:Simple /loc:""%TEMP%\PACKAGE"" '''" > %TEMP%\PACKAGE\PACKAGE.bat

GOTO EXPORT

:SPECIFIC
sqlcmd -S %1,%SPORT% -U <DBADMIN> -P <PASSWORD> -Q "IF DB_ID('%3') IS NULL RETURN ELSE PRINT 'SQLPACKAGEr.exe /s1:%1,%SPORT% /u1:<DBADMIN> /p1:<PASSWORD> /db1:%3 /n:%3 /exe /q /recoverymodel:Simple /excludedata:Table /loc:""%TEMP%\PACKAGE"" '" > %TEMP%\PACKAGE\PACKAGE.bat

GOTO EXPORT


:CREATE_DIR
MKDIR "%TEMP%\PACKAGE"
GOTO CHECK_VARIABLES

:DLTFILES
DEL %TEMP%\PACKAGE\*.* /Q
GOTO CHECK_VARIABLES

:USAGE
ECHO "Proper use: %0 [Source Server] [Destination Server] [(All)/<DBNAME>], [Source Server Port # (1433)] [Destination Server Port # (1433)]"
GOTO END

:SOURCE
ECHO The Source and Destination servers cannot be the same!
GOTO END


:EXPORT
CALL %TEMP%\PACKAGE\PACKAGE.bat

GOTO DEPLOYDB

:DEPLOYDB

CD %TEMP%\PACKAGE
for /f "delims=" %%a in ('dir /b "%List%"') do (

sqlcmd -S %2,%DPORT% -U <DBADMIN> -P <PASSWORD> -Q "if DB_ID('%%~na') IS NOT NULL DROP DATABASE %%~na"
%%~na /server:%2,%DPORT% /database:%%~na /quiet /username:<DBADMIN> /password:<PASSWORD>
)
GOTO END

:END
@ECHO ON

---

Thanks,

Pasha