Posted by : Arjun Lagisetty Wednesday, September 14, 2016

Thanks to my very descriptive title. I can skip the obligatory introduction statement. 

Requirement:

Backup SnapShot Fact tables after every snapshot load. 
Archive the backups with timestamp filename.

Solution:

Download the script and run the following command

.\backup_sql_tables_ps.ps1 -ServerInstance INSTANCE_NAME -Database DATABASE_NAME -ExportPath C:\temp\ -TableNames DATABASE_NAME.schema.TABLE1,DATABASE_NAME.schema.TABLE2,DATABASE_NAME.schema.TABLE3

This Script uses BCP to backup SQL server Objects. 
This script should be run in a machine where sql server is installed with a user which has privileges and access to BCP ulitily provided with Sql Server
This script needs 7Zip installed in $env:ProgramFiles\7-Zip\
It will create folders called MMddyyyyHHmmsdata for data files
It will create folders called MMddyyyyHHmmserror for error and info detail files in the archive
Each table's data is backed up in a file called TableName.bat (If you are trying to backup two tables with same name from different schema this might not work for you.)
If archive name is not provided it create archive with timestamp MMddyyyyHHmms.7z
Sample Command

ODI Specific:

This can command can be called from ODI if you have ODI and SQL server installed in the same machine and it should work with out a sweat.

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty