How to Back up a Single Table With Its Data From a Database

1. Introduction

Backing up a single table with its data is essential for preserving specific records without exporting the entire database. This approach isolates essential information, facilitating data migration, recovery, and analysis. Different database systems offer different methods to perform this task, enabling efficient table-level backups tailored to specific needs.

In this tutorial, we’ll explore various techniques to back up a single table in PostgreSQL, MySQL, and SQL Server. Additionally, we’ll provide practical steps and examples using the Baeldung University database.

2. Implementation in PostgreSQL

PostgreSQL provides several ways to back up a single table along with its data. One efficient approach involves using the pg_dump utility. Furthermore, this utility exports the schema and data in a format that supports easy restoration. Let’s consider the Student table from the Baeldung University database for demonstration.

The pg_dump utility simplifies the process of backing up a single table. Additionally, this tool generates an SQL script or a binary archive that captures both the structure and the data of a table.

For example, let’s backup only the Student table in the Baeldung University database:

$ pg_dump -h localhost -p 5432 -U user -d university -t student -f student_backup.sql

Here’s the meaning of the options in the command:

• -h localhost: Specifies the server host
• -p 5432: Defines the port number for PostgreSQL
• -U user: Specifies the database user
• -d university: Defines the database name
• -t student: Targets a specific table for backup
• -f student_backup.sql: Outputs the backup to a file named student_backup.sql

This method creates a portable SQL file containing the table schema and its data, which can be restored later. Moreover, the file is stored on the host machine.

To ensure the backup was successful, we can inspect the file’s contents using the cat command:

$ cat student_backup.sql
--
-- PostgreSQL database dump
--
...
COPY public.student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) FROM stdin;
1001    John Liu        123345566       2001-04-05      2020-01-15      2024-06-15      4
1003    Rita Ora        132345166       2001-01-14      2020-01-15      2024-06-15      4.2
1007    Philip Lose     321345566       2001-06-15      2020-01-15      2024-06-15      3.8
...

--
-- Name: student student_pkey; Type: CONSTRAINT; Schema: public; Owner: user
--

ALTER TABLE ONLY public.student
    ADD CONSTRAINT student_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete

This command displays the SQL statements that define the Student table structure and the INSERT statements containing the table’s data. If the backup is successful, it should include the complete schema definition and the associated data from the Student table.

3. Implementation in MySQL

MySQL also provides multiple ways to back up a single table along with its data. One of the most common and effective methods is using the mysqldump utility.

For example, let’s back up only the Student table from the Baeldung University database:

$ mysqldump -h localhost -P 3306 -u user -p university student > student_backup.sql

Here’s the meaning of each part of the command:

• -h localhost: Specifies the MySQL server host
• -P 3306: Defines the port number for MySQL
• -u user: Indicates the MySQL username
• -p: Prompts for the user’s password
• > student_backup.sql: Redirects the backup to a file named student_backup.sql

This command generates an SQL dump file that contains the schema and records of the Student table.

To confirm that the backup was successful, we can inspect the contents of the student_backup.sql file using the cat command:

$ cat student_backup.sql
--
-- Table structure for table `student`
--
...
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `national_id` VARCHAR(20) UNIQUE,
  `birth_date` DATE,
  `enrollment_date` DATE,
  `graduation_date` DATE,
  `gpa` DECIMAL(3,2),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
...

--
-- Dumping data for table `student`
--

INSERT INTO `student` VALUES 
(1001,'John Liu','123345566','2001-04-05','2020-01-15','2024-06-15',4.00),
(1003,'Rita Ora','132345166','2001-01-14','2020-01-15','2024-06-15',4.20),
(1007,'Philip Lose','321345566','2001-06-15','2020-01-15','2024-06-15',3.80);
...

--
-- Dump completed
--

The output should include the table’s creation script and INSERT statements for all rows. This confirms that the Student table and its contents have been successfully backed up.

4. Implementation in SQL Server

SQL Server offers multiple methods to back up a single table along with its data. Furthermore, these methods include using the mssql-scripter utility, the sqlpackage utility, and copying the table to a temporary database.

4.1. Using mssql-scripter Utility

This utility is a command-line tool that generates SQL scripts for SQL Server objects, including tables and their data. Additionally, it’s a versatile tool for backing up a single table in SQL Server.

To use the tool, let’s first install mssql-scripter if it’s not already installed:

$ pip install mssql-scripter

Next, let’s use the command to backup the Student table:

$ mssql-scripter -S localhost -d university -U user -P password --script-create --script-data --include-objects student --output-file student_backup.sql
...
Generating CREATE TABLE script for: student...
Generating INSERT scripts for: student...
Writing scripts to file: student_backup.sql...
Scripting completed successfully.
Backup file created: student_backup.sql

Here’s a breakdown of the commands:

• -S localhost: Specifies the SQL Server instance
• -d university: Defines the database name
• -U user: Indicates the SQL Server username
• -P password: Specifies the user’s password
• –script-create: Generates the table creation script
• –script-data: Includes the table’s data in the script
• –include-objects student: Targets the Student table
• –output-file student_backup.sql: Outputs the backup to a file named student_backup.sql

The command generates an SQL file with the backed up database table.

Furthermore, to confirm that the backup was successful, we inspect the content of the backup file:

$ cat student_backup.sql
...
-- Table structure for table `student`
CREATE TABLE student (
    id INT PRIMARY KEY,
    name NVARCHAR(255) NOT NULL,
    national_id NVARCHAR(20) UNIQUE,
    birth_date DATE,
    enrollment_date DATE,
    graduation_date DATE,
    gpa DECIMAL(3,2)
);

-- Data for table `student`
INSERT INTO student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
VALUES 
(1001, 'John Liu', '123345566', '2001-04-05', '2020-01-15', '2024-06-15', 4.00),
(1003, 'Rita Ora', '132345166', '2001-01-14', '2020-01-15', '2024-06-15', 4.20),
(1007, 'Philip Lose', '321345566', '2001-06-15', '2020-01-15', '2024-06-15', 3.80);
...

Similarly, the output should include the table’s creation script and INSERT statements for all rows. This confirms that the Student table and its contents have been successfully backed up.

4.2. Using sqlpackage Utility

We can use the sqlpackage utility to export and import SQL Server databases or specific objects.

For example, let’s export the Student table:

$ sqlpackage /Action:Export /SourceServerName:localhost /SourceDatabaseName:university /SourceUser:user /SourcePassword:password /TargetFile:student_backup.bacpac /Table:student
Microsoft (R) SQLPackage Version 16.0.6161.0
Copyright (C) 2022 Microsoft. All rights reserved.
...
Export completed successfully.
Creating bacpac file 'student_backup.bacpac'...
Bacpac file created successfully.
Export process completed.

Here’s a breakdown of what the command does:

• /SourceServerName:localhost: Specifies the host
• /SourceDatabaseName:university: Specifies the database name
• /SourceUser:user: Specifies the database user
• /SourcePassword:password: Specifies the user’s password
• /TargetFile:student_backup.bacpac: Saves the backup to a file named student_backup.bacpac
• /Table:student: Targets the Student table

We can verify the backup by checking the student_backup.bacpac file.

4.3. Copying a Table to a Temporary Database

Another approach is to copy the table to a temporary database, which can act as a backup. This method is useful for quick backups or when working with large datasets.

First, let’s create a temporary database to hold the copied table:

CREATE DATABASE TempBackupDB;

Next, we use the SELECT INTO statement to copy the Student table from the University database to the temporary database:

USE TempBackupDB;
SELECT * INTO Students FROM university.dbo.Student;

This command creates a new table named Students in the TempBackupDB database and copies all the data from the Student table in the University database.

Finally, let’s perform a full backup of the temporary database to create a .bak file:

BACKUP DATABASE TempBackupDB TO DISK = 'C:\Backup\TempBackupDB.bak';

This command creates a backup file named TempBackupDB.bak at the specified location (C:\Backup).

5. Conclusion

In this article, we explored various methods to back up a single table in PostgreSQL, MySQL, and SQL Server. Each method provides a reliable way to preserve table-specific data for migration, recovery, or analysis. By following the practical steps and examples provided, we can efficiently back up and restore critical data tailored to our specific database system.