Packaging Multiple Databases

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

Packaging Multiple Databases

Postby pasha » Tue Jun 10, 2008 8:15 pm

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
pasha
 
Posts: 4
Joined: Wed May 28, 2008 2:38 am
Location: Mt. View, CA

Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests