Including and Excluding Objects
You specify objects to include in backup, restore, and replicate operations with the vbr
configuration and command-line parameters includeObject
s and ‑‑include-objects
, respectively. After you specify the objects to include in the operation, you can optionally specify a set of objects to exclude from the same operation with the vbr
configuration and command line parameters excludeObjects
and ‑‑exclude-objects
, respectively. In both cases, you can use wildcard expressions to include and exclude groups of objects. Wildcards are supported in vbr.ini
files and vbr
command line parameters.
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
Character | Description |
---|---|
? | Matches any single character. Case-insensitive. |
* | Matches 0 or more characters. Case-insensitive. |
\ | Escapes the next character. To include a literal ? or * in your table or schema name, use the \ character immediately before the escaped character. To escape the \ character itself, use a double \\. |
" | Escapes the . character. To include a literal . in your table or schema name, wrap the character in double quotation marks. |
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 includes a schema and the schema reference omits any 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 could restore all of the 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.
Take extra care when using wildcard patterns to restore database objects. Depending on your object restore mode settings, restored objects can overwrite existing objects. Test the impact of a wildcard restore with the ‑‑dry-run
vbr
parameter before performing the actual task.
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.