Skip to content

Extract tables from a database backup file

Shipped or downloaded SQL database backup files for a VIP Platform environment contain a copy of the environment’s entire database. 

If needed, a specific table—or set of tables—can be extracted from the database file and saved to a new and separate SQL database file on a user’s local machine. The newly created file can be imported to a local development environment or be used for other purposes.

Prerequisite

A SQL database backup file must be downloaded and available on the user’s local machine.

List tables

Retrieve a list of tables that exist in a downloaded backup SQL database file:

grep -E '^-- Table structure' <file-name>.sql

An example request and output for the list of tables in a downloaded file backup.sql:

$ grep -E '^-- Table structure' backup.sql
-- Table structure for table `wp_a8c_cron_control_jobs`
-- Table structure for table `wp_blogmeta`
-- Table structure for table `wp_blogs`
-- Table structure for table `wp_commentmeta`
-- Table structure for table `wp_comments`
-- Table structure for table `wp_links`
-- Table structure for table `wp_options`
-- Table structure for table `wp_postmeta`
-- Table structure for table `wp_posts`
-- Table structure for table `wp_signups`
-- Table structure for table `wp_site`
-- Table structure for table `wp_sitemeta`
-- Table structure for table `wp_term_relationships`
-- Table structure for table `wp_term_taxonomy`
-- Table structure for table `wp_termmeta`
-- Table structure for table `wp_terms`
-- Table structure for table `wp_usermeta`
-- Table structure for table `wp_users`

Extract a specific table

To extract a specific table, the line numbers in the SQL file where the table exists must first be identified. Use those identified line numbers to target and extract the table to a separate file.

Identify the lines in the file that contain the table

Adding the -n option to the “list tables” command will include line numbers in the command output. Piping that command output to grep -A1 <table-name> will report the line number where the selected table begins, as well as the line number where the next table in the SQL file begins.

grep -n -E '^-- Table structure' <file-name>.sql | grep -A1 <table-name>

In this example, the command is formatted to identify the line where the table wp_posts begins, and the table that follows it, in a backup file named backup.sql:

$ grep -n -E '^-- Table structure' backup.sql | grep -A1 wp_posts
3793:-- Table structure for table `wp_posts`
3842:-- Table structure for table `wp_signups`

The example command output indicates that the wp_posts table begins on line 3793, and ends at line 3841 (one line before the wp_signups table begins).

Extract the table with the identified line numbers

Target a table by using the beginning and ending line number values and extract the table to a new SQL file.

sed -n '<beginning>,<ending> p' <file-name>.sql > new-file.sql

In this command example, a table is extracted from a backup file named backup.sql using the beginning line number 3793 and the ending line number 3841. The table is then saved to a newly created file named new-file.sql:

sed -n '3793,3841 p' backup.sql > new-file.sql

Extract multiple tables

Multiple tables can be extracted from a downloaded backup file and appended to a single SQL file.

Identify the lines in the file that contain each table

For each table to be extracted from the downloaded backup file, identify the beginning and ending lines for a table.

grep -n -E '^-- Table structure' <file-name>.sql | grep -A1 <table-name>

Repeat the above command for each table to be extracted.

Extract specific tables and append to a single file

Extract the first table to a new SQL file:

sed -n '<beginning>,<ending> p' <file-name>.sql > new-file.sql

Extract additional tables in separate commands and append them to the new SQL file using >>.

sed -n '<beginning>,<ending> p' <file-name>.sql >> new-file.sql

Extract tables for a subsite on a multisite

Database backup files for WordPress multisite environments include all tables for all subsites. If only the tables for a single subsite are needed, those tables can be extracted from the backup file and saved to their own separate file.

A subsite’s tables in a SQL file are structured with the table_prefix wp_ followed by the subsite’s ID. All of a subsite’s tables in a database backup file can be identified and extracted using a combination of the steps listed above.

Identify the lines that contain the subsite tables

Identify the beginning line number in a SQL file for each table belonging to a specific subsite ID.

grep -n -E '^-- Table structure' <file-name>.sql | grep -A1 wp_[ID]

In this example, the command is formatted to identify the first line of each table belonging to subsite ID 2 in a backup file named backup.sql:

$ grep -n -E '^-- Table structure' backup.sql | grep -A1 wp_2
3059:-- Table structure for table `wp_2_a8c_cron_control_jobs`
3094:-- Table structure for table `wp_2_commentmeta`
3121:-- Table structure for table `wp_2_comments`
3163:-- Table structure for table `wp_2_links`
3198:-- Table structure for table `wp_2_options`
3226:-- Table structure for table `wp_2_postmeta`
3254:-- Table structure for table `wp_2_posts`
3303:-- Table structure for table `wp_2_term_relationships`
3329:-- Table structure for table `wp_2_term_taxonomy`
3359:-- Table structure for table `wp_2_termmeta`
3386:-- Table structure for table `wp_2_terms`
3414:-- Table structure for table `wp_a8c_cron_control_jobs`

The example command output indicates that tables belonging to subsite ID 2 begin on line 3059, and end at line 3413 (one line before the wp_a8c_cron_control_jobs table begins).

Extract the subsite’s tables with the identified line numbers

Using the beginning and ending line number values, extract the subsite’s table to a new SQL file.

sed -n '<beginning>,<ending> p' <file-name>.sql > new-file.sql

Using the output from the command example above, the tables for subsite ID 2 are extracted from a backup file named backup.sql using the identified beginning line number 3059 and the ending line number 3413. The tables are then saved to a newly created file named new-file.sql:

sed -n '3059,3413 p' backup.sql > new-file.sql

Validating the structure of SQL files

New SQL files created by extracting tables from a database backup file should be validated before using them for import. As a first step, verify that the new file includes the expected tables from the extraction process.

grep -E '^-- Table structure' <file-name>.sql

Retrieve the line count of a SQL file

Run the wc command against the newly created SQL file to retrieve the number of lines contained in the file.

wc -l <file-name>.sql

Validate a SQL file with VIP-CLI

Prerequisite

VIP-CLI is installed and has been updated to the most current version.

Verify that a SQL file is correctly formatted for import with the VIP-CLI command:

vip import validate-sql <file-name>.sql

The output from the import validate-sql will report existing file formatting issues as well as a line count. The line count reported by VIP-CLI includes one blank line in a SQL file that the wc command ignores.

Last updated: May 06, 2022