Support

Documentation

RegEx Database Tables Exclusion

[Note]Note

This feature is available only in Akeeba Backup Professional

Sometimes you know that you have to exclude database tables following a specific naming pattern, but they are so many that it's impractical going to the normal exclusion filters page and click them one by one. Or, more frequently, you want to exclude database tables not following a specific pattern, e.g. tables whose name doesn't begin with your site's table naming prefix. Regular expression filters let you create pattern-based filters to deal with that. What are regular expressions? Let's consult Wikipedia:

 

In computing, regular expressions, also referred to as regex or regexp, provide a concise and flexible means for matching strings of text, such as particular characters, words, or patterns of characters. A regular expression is written in a formal language that can be interpreted by a regular expression processor, a program that either serves as a parser generator or examines text and identifies parts that match the provided specification.

 
 --"Regular expression" article from Wikipedia

In a nutshell, regular expressions allow you to quickly define filters which span multiple subdirectories and match file or directory names based on a number of criteria. If you want a quick cheatsheet you can use, we recommend taking a look at the Regular Expressions Cheat Sheet (V2) from Cheatography. Some practical examples will be presented at the end of this section.

There are some special considerations experienced regular expressions users must have in mind:

  • You are supposed to specify a full regular expression, including its opening and ending separators. So ^foo is invalid, but /^foo/ and #^foo# are valid.

  • Akeeba Backup supports an extension to the PCRE syntax. If you prefix the regex with an exclamation mark you negate its meaning. So /^foo/ will match all entities starting with foo, whereas !/^foo/ will match all entities NOT starting with foo.

  • Akeeba Backup stores and parses your data as raw Unicode (UTF-8), provided that your database meets the minimum requirement of site database server version. This eliminates the need to use the u suffix of regular expressions in order to reference Unicode characters.

When it comes to database table filters in particular, you have to bear in mind:

  • All Joomla!™ tables have their prefix stripped and replaced by the standard #__ placeholder. So, if your database prefix is abc1_, the table abc1_users is internally referenced as #__users. This is called the "abstracted" name in Akeeba Backup's documentation. You must take this into account when writing regex filters. The abstracted name of the table is the name you will have to match with your regular expressions!

  • The prefix replacement described above takes place in Full Site and All Configured Databases backup modes. However, it does not take place in the Database Only backup mode. As a result, you have to reference the tables by their full, normal name, e.g. abc1_users.

  • The examples at the end of this section apply to a full site backup scenario, where the replacement does take place.

RegEx Database Tables Exclusion

At the very top of the page you will see the backup profile number and title as a reminder. Remember that database table exclusion filters, just like all Akeeba Backup filters, are set up per backup profile.

Below that you can find the Current Database drop-down menu. Akeeba Backup can define filters for the site's main database or for each of the extra databases you may have defined. The default selection, Site's main database, contains all filters pertaining to the main site's database, of course. If you have defined extra databases, you can select the appropriate database from the drop-down list in order to define filters for that database.

Each row represents a filter. It has three columns:

Icons column

You can perform the basic operations by clicking on this column's icons:

  • Trashcan. When you click it, the filter row will be removed.

  • Pencil. When you click it, the row switches to edit mode

  • Add (only on the last row). Clicking this icon adds a new row at the end of the list and switches it to edit mode. You can select the type of the newly added filter.

Type

The filter type defines what will happen when a directory or file matches the regex filter and can be one of:

  • Exclude a table. Completely skips backing up tables whose names match the regular expression.

  • Do not backup a table's contents. Only backs up the structure of tables whose names match the regular expression, but not their contents.

Filter Item

This is the actual regular expression you have to write.

RegEx Database Tables Exclusion - Edit Mode

When you click on the pencil or add icons, the respective row enters the edit mode. In this mode, the filter type becomes a drop-down list where you can select the type of this filter row. The filter item column also turns into an edit box so that you can enter your filter definition. The icon column now contains two different icons:

  • Disk. When you click it, the changes will be saved.

  • Cancel. When you click it, any changes will be cancelled and the row will resume its previous state.

You can make sure that your filters match the tables you meant to. Just go back to the Control Panel and click on the Database Tables Exclusion button. The items filtered out by the regular expressions filters will be automatically highlighted in red. You can browse through the database structure to make sure that only the items you really meant are being excluded.

Regular Expressions recipes for database tables

No matter how good you are on writing regular expressions, it's always a good idea to have some recipes which serve as a starting point for cooking your own.

  1. Exclude non-Joomla! database tables

    !/^#__/

  2. Exclude Akeeba Backup tables. We know that these tables have ak_ in their name after the table prefix, e.g. abc1_ak_foobar becomes #__ak_foobar, so you only need to filter #__ak.

    /^#__ak_/