Windows ETL Server: Repository Backup Automation

This blog article covers automating the backup of DAC and Informatica repositories in a Windows ETL Server environment. The batch files which perform the backup tasks have been tested with DAC 10.1.3.4.1 and Informatica 8.6 on a Windows Server 2003 environment.

The batch files utilize Date/Time Windows environment variables which might not be available in earlier versions of Windows, such as NT/2000. The batch files need to reside on the ETL server, and can be scheduled using the default Windows task scheduler located under Programs > Accessories > Scheduled Tasks.

The Informatica batch file connects via command line to Informatica and then executes a backup of the repository.

Informatica Syntax:
pmrep connect -r Repository_name -d Domain_name -n user -x passwd
pmrep backup -o output_file_name (**by default outputs to the infa_shared/Backup folder).

The DAC batch file utilizes the AutomationUtils.bat file located in the DAC folder, and uses the EXPORT command. You can specify specific Source Containers by adding them to the end of the command-line, as shown in the DAC documentation.

DAC Syntax:
EXPORT 'folderName' 'contName1' 'contName2' ...

Below are simple batch files that 1) Create a new folder with the current date/time in a specified location and 2) export INFA/DAC repositories and place them in these folders.


INFORMATICA REPOSITORY BACKUP SAMPLE SCRIPT:
::*****************************************************************************
:: Set variables
::*****************************************************************************

set /a dt_tm_stamp=0
set hour=%time:~0,2%
if %hour% LSS 10 set hour=0%hour:~-1%
set dt_tm_stamp=%date:~-4%%date:~-10,2%%date:~-7,2%%hour%%time:~3,2%

::*****************************************************************************
:: Navigate to backup location, and create new folder with todays datetime stamp.
::*****************************************************************************

CD C:\ETL_BACKUPS\
md INFA_Repository_%dt_tm_stamp%

::*****************************************************************************
:: Connect to Informatica and backup repository
::*****************************************************************************

pmrep connect -r BICG_BI_DW -d Domain_brian-9574f410e -n Administrator -x Administrator
pmrep backup -o BICG_BI_DW_%dt_tm_stamp%.rep

::*****************************************************************************
:: Move Informatica repository into directory previously created with todays date.
::*****************************************************************************

cd C:\Informatica\PowerCenter8.6.0\server\infa_shared\Backup
MOVE C:\Informatica\PowerCenter8.6.0\server\infa_shared\Backup\BICG_BI_DW_%dt_tm_stamp%.rep C:\ETL_BACKUPS\INFA_Repository_%dt_tm_stamp%\

::*****************************************************************************
:: Clear out variables
::*****************************************************************************

set dt_tm_stamp=
:EXIT

DAC REPOSITORY BACKUP SAMPLE SCRIPT:
::*****************************************************************************
:: Set variables
::*****************************************************************************

set /a dt_tm_stamp=0
set hour=%time:~0,2%
if %hour% LSS 10 set hour=0%hour:~-1%
set dt_tm_stamp=%date:~-4%%date:~-10,2%%date:~-7,2%%hour%%time:~3,2%


::*****************************************************************************
:: Navigate to backup location, and create new folder with todays datetime stamp.
::*****************************************************************************

CD C:\ETL_BACKUPS
md DAC_Repository_%dt_tm_stamp%

::*****************************************************************************
:: Export DAC repository
::*****************************************************************************

CD C:\oracleBI\DAC\bifoundation\DAC\
call AutomationUtils.bat EXPORT C:\ETL_BACKUPS\DAC_Repository_%dt_tm_stamp%

::*****************************************************************************
:: Clear out variables
::*****************************************************************************

set dt_tm_stamp=
:EXIT

-----------------------------------------------------

This is the most basic resolution and one should incorporate a backed-up shared drive location for the repositories to be stored on, as well as a way to trim the repositories after a specified amount of time as these files can be several hundred MB each.