SQL Query to Create a Backup Table

Relational databases play an important role in managing data, especially during complex operations like updates and deletions. To maintain data integrity, it is essential to back up tables before making changes. SQL backup tables ensure the safety of the original dataset, allow for data recovery, and facilitate safe experimentation.

Importance of Creating Backup Tables in SQL

Backup tables are essential during complex data changes or migrations because they let us safely restore the original data if something goes wrong. Here are the key reasons why creating them is essential:

• Data Integrity: Protecting the original dataset from accidental loss or corruption.
  
• Disaster Recovery: Ensuring that the data can be restored in case of failure or errors during modifications.
  
• Safe Experimentation: Making changes to data without affecting the live dataset.
  
• Data Migration: Moving data from one system or table to another without losing original records.

Demo Table: Student Information

We will be using the following table "Student Information" which consists of data of Geeks who enrolled in our DSA course as shown below:

How to Create Backup Tables in SQL

We can create a backup of a table by creating a duplicate or copy of original database. This is particularly useful for preserving the original table before performing updates, deletions, or other modifications. Below is a detailed explanation of the syntax and terms used for this operation.

Syntax

CREATE TABLE Table_Name AS SELECT * FROM Source_Table_Name;

Key Terms

• Table_Name: Specifies the name of the new backup table.
  
• AS: Acts as an alias, enabling the SQL query to copy data from the source table into the backup table.

Examples of SQL Backup Table Creation

Creating backup tables in SQL can involve duplicating all data, copying specific columns, or even creating an empty table with the same structure. Let's look at some examples on how to copy/duplicate table in SQL to create a backup table in different scenarios:

1. Backup Table with All Columns and Data

In this example, we will create a backup table "stud_1" of "student_information" table by creating a copy of "student_information" table that duplicates all columns and their data..

Query:

CREATE TABLE stud_1 AS SELECT * FROM student_information;
SELECT * FROM stud_1;

Output

2. SQL Backup Table with Specific Column Data Example

In this example, we create a backup table, "stud_2", by copying only selected columns from the "student_information" table using a SELECT statement.

Query:

CREATE TABLE stud_2 AS
SELECT id,student_name FROM student_information;
SELECT * FROM stud_2;

Output

Till now we have seen how to create a  clone of the source table. In the above backup table, the data is also copied along with the table. However, we can also create a backup table without copying the data.

3. Backup Table Without Data

So, to create a table without any data being copied we can use the WHERE clause which needs to return a FALSE value. For example, we can use WHERE 2<2 or WHERE 1=2.

In this example, we will create a backup table "geeks_student" of "student_information" table by creating a copy of "student_information" table and copying its all columns without data.

Query:

CREATE TABLE geeks_student AS SELECT * FROM student_information
WHERE 1!=1;
SELECT * FROM geeks_student;

Output

4. Backup Table with Specific Columns and No Data

In this example, we will create a backup table "geek_student" of "student_information" table by creating a copy of "student_information" table and copying specific columns without data.

Query:

CREATE TABLE specific_empty_backup AS
SELECT ID, Student_Name FROM student_information WHERE 1=2;
SELECT * FROM specific_empty_backup;

Output

Limitations of CREATE TABLE AS SELECT

While convenient, CREATE TABLE AS SELECT has key limitations:

• Constraints Are Not Copied: Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT are not carried over to the new table.
  
• Indexes and Triggers Are Not Copied: Any indexes or triggers from the base table won't transfer to the backup
  
• Manual Effort Required for Full Duplication: To fully replicate the original table structure including constraints and indexes, manual setup is necessary.

How to Create Backup Tables with Constraints

To copy the structure of a table along with its constraints, the CREATE TABLE AS SELECT statement is not sufficient. Instead, we need to define the table structure manually and then copy the data.

Example: Adding Constraints After Table Creation

1. Create a table without constraints:

CREATE TABLE student_backup AS SELECT * FROM student_information;

2. Add constraints to the backup table

ALTER TABLE student_backup ADD PRIMARY KEY (ID);

Example: Define Table with Constraints and Copy Data

1. Define the new table with constraints

CREATE TABLE student_backup (
    ID INT PRIMARY KEY,
    Age INT,
    Student_Name VARCHAR(50),
    Sex VARCHAR(10)
);

2. Copy data into the new table

 INSERT INTO student_backup SELECT * FROM student_information;

Conclusion

Creating backup tables in SQL is an essential practice for ensuring data integrity, facilitating disaster recovery, and safely experimentation without compromising the original data. While the CREATE TABLE AS SELECT statement is a quick and convenient method, it has limitations such as the exclusion of constraints, indexes, and triggers. For a more complete backup, manual intervention is required to replicate the full structure of the original table.