Extract tables from a SQL database file

SQL database backup files for a VIP Platform environment can be downloaded from the VIP Dashboard. Database backup files can include a copy of the entire database, only the tables for a specific network site, or a specific set of one or more tables.

If needed, a specific table—or set of tables—can be extracted from a downloaded 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.

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 network site on a multisite

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

A network site’s tables in a SQL file are structured with the table_prefix wp_ followed by the site’s ID. All of a network site’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 network site tables

Identify the beginning line number in a SQL file for each table belonging to a specific network site 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 site 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 site ID 2 begin on line 3059, and end at line 3413 (one line before the wp_a8c_cron_control_jobs table begins).

Extract the network site’s tables with the identified line numbers

Using the beginning and ending line number values, extract the network site’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 site 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

Verify that a SQL file is correctly formatted for import with the VIP-CLI command: vip import validate-sql [options]

The output from vip 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.