Red Gate forums :: View topic - Packaging Multiple Databases
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

Packaging Multiple Databases

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
pasha



Joined: 28 May 2008
Posts: 4
Location: Mt. View, CA

PostPosted: Tue Jun 10, 2008 8:15 pm    Post subject: Packaging Multiple Databases Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group