VSys One: Volunteer Management Software

Command Line Datapump

For organizations with production and test databases (you do have a test environment, don't you?), copying updated production data into the test environment can be cumbersome. Rather than having to back up the production data and restore it into the testing database, you can use the datapump command line tool.

Simple Datapump

1. Determine the source and target databases. A database's name can be either its backend plus a colon plus name, e.g. "SQL Server: Production" or its nickname. In the example below the nickname is "demo1".
   
2. Make a backup. When you run this task, VSys does not verify that you aren't moving the wrong database to an even more wrong location, e.g. an evaluation database onto your production database. So back up the production database too, just in case.
3. Get to a Windows command line in the folder that VSys.exe and VSys.ini are located.
4. Enter the command line
   VSys.exe datapump:source,target
   Where source is the production database's name or nickname, and target is the test database's name or nickname.
   
   Or:
   VSys.exe datapump:source,target,table1,table2,table3
   Where source is the production database's name or nickname, target is the test database's name or nickname, and the tableX options (one or more of them) are the names of the tables to be copied. If no tables are specified then all tables are copied.
   
   Or: when providing a list of tables, you can also exclude a table by prefixing its name with a "-". For example,
   VSys.exe datapump:source,target,-trace,-voxilogs,-zips
   Would move all tables except trace, voxilogs and zips.
5. For each VSys-recognized table in the source database, VSys will delete that table in the target database and re-create it with the contents of the table from the source database.

Datapump with a settings file

Datapump can also be launched using a specially-formatted XML file. The command prompt syntax is:
VSys.exe datapump:settings:c:\VSys\filename.xml

or, to include the source and target connection names on the command line,
VSys.exe datapump:source,target,settings:c:\VSys\filename.xml

The settings XML should be formatted as in:
<DATAPUMP Source="sstemp2" Target="temp2">
<TABLES>address, appFields, applications, assignments, attachments, availability, availGroups,awards, blacklist, certifs, comments, courses, creds, entries, events, groups, hours, interviews, letters, lists, locations, mandates, memberships, money, nindex, notifications, options, optionValues, people, relationships, reports, schedule, slots, surveys, tags, trace, training, transitions, webapps, webpends</TABLES>
<FILTER Table="trace" MinDate="2014-01-01"/>
<FILTER Table="letters" MinDate="2014-01-01"/>
</DATAPUMP>

• Source and Target, if provided, override the equivalent command line parameters.
• <TABLES> to indicate the table names to be pumped, separated by commas. If none are provided, all tables will be processed; does not support the "-" (exclude) syntax.
• Zero or more <FILTER> elements limit the records moved from specific tables to only those on or after the given date. Tables supporting date-based filtering are: attachments, letters, trace, notifications, transitions and voxilogs. Filters specified for any other tables will be ignored.