Including and excluding objects

You specify objects to include in backup, restore, and replicate operations with the vbr configuration and command-line parameters includeObjects and --include-objects, respectively. You can optionally modify the set of included objects with the vbr configuration and command line parameters excludeObjects and --exclude-objects, respectively. Both parameters support wildcard expressions to include and exclude groups of objects.

For example, you might back up all tables in the schema store, and then exclude from the backup the table store.orders and all tables in the same schema whose name includes the string account:

vbr --task=backup --config-file=db.ini --include-objects 'store.*' --exclude-objects 'store.orders,store.*account*'

Wildcard characters

Matching schemas

Any string pattern without a period (.) character represents a schema. For example, the following includeObjects list can match any schema name that starts with the string customer, and any two-character schema name that starts with the letter s:

includeObjects = customer*,s?

When a vbr operation specifies a schema that is unqualified by table references, the operation includes all tables of that schema. In this case, you cannot exclude individual tables from the same schema. For example, the following vbr.ini entries are invalid:

; invalid:
includeObjects = VMart
excludeObjects = VMart.?table?

You can exclude tables from an included schema by identifying the schema with the pattern schemaname.*. In this case, the pattern explicitly specifies to include all tables in that schema with the wildcard *. In the following example, the include-objects parameter includes all tables in the VMart schema, and then excludes specific tables—specifically, the table VMart.sales and all VMart tables that include the string account:

--include-objects 'VMart.*'
--exclude-objects 'VMart.sales,VMart.*account*'

Matching tables

Any pattern that includes a period (.) represents a table. For example, in a configuration file, the following includeObjects list matches the table name sales.newclients, and any two-character table name in the same schema:

includeObjects = sales.newclients,sales.??

You can also match all schemas and tables in a database or backup by using the pattern *.*. For example, you can restore all tables and schemas in a backup using this command:

--include-objects '*.*'

Because a vbr parameter is evaluated on the command line, you must enclose the wildcards in single quote marks to prevent Linux from misinterpreting them.

Testing wildcard patterns

You can test the results of any pattern by using the --dry-run parameter with a backup or restore command. Commands that include --dry-run do not affect your database. Instead, vbr displays the result of the command without executing it. For more information on --dry-run, refer to the vbr reference.

Using wildcards with backups

You can identify objects to include in your object backup tasks using the includeObjects and excludeObjects parameters in your configuration file. A typical configuration file might include the following content:

[Misc]
snapshotName = dbobjects
restorePointLimit = 1
enableFreeSpaceCheck = True
includeObjects = VMart.*,online_sales.*
excludeObjects = *.*temp*

In this example, the backup would include all tables from the VMart and online_sales schemas, while excluding any table containing the string 'temp' in its name belonging to any schema.

After it evaluates included objects, vbr evaluates excluded objects and removes excluded objects from the included set. For example, if you included schema1.table1 and then excluded schema1.table1, that object would be excluded. If no other objects were included in the task, the task would fail. The same is true for wildcards. If an exclusion pattern removes all included objects, the task fails.

Using wildcards with restore

You can identify objects to include in your restore tasks using the --include-objects and --exclude-objects parameters.

As with backups, vbr evaluates excluded objects after it evaluates included objects and removes excluded objects from the included set. If no objects remain, the task fails.

A typical restore command might include this content. (Line wrapped in the documentation for readability, but this is one command.)

$ vbr -t restore -c verticaconfig --include-objects 'customers.*,sales??'
    --exclude-objects 'customers.199?,customers.200?'

This example includes the schema customers, minus any tables with names matching 199 and 200 plus one character, as well as all any schema matching 'sales' plus two characters.

Another typical restore command might include this content.

$ vbr -t restore -c replicateconfig --include-objects '*.transactions,flights.*'
    --exclude-objects 'flights.DTW*,flights.LAS*,flights.LAX*'

This example includes any table named transactions, regardless of schema, and any tables beginning with DTW, LAS, or LAX belonging to the schema flights. Although these three-letter airport codes are capitalized in the example, vbr is case-insensitive.