SQL Backup Table

[Linux Hint]

Backups are some of the most important features in disaster recovery. When it comes to databases, backups become even more important. This is because the operations in a database are permanent and once the data is changed or modified, there is no rollback.

There are various methods of backing up the data in SQL. This can include backing up the entire server, the entire schema, database, etc. However, for a more granular backup, we can backup the individual table to preserve the state of the data in that table.

In this tutorial, we will explore the various methods and techniques that we can use to backup the tables in SQL.

It is good to keep in mind that in the methods of backing up the data, even unified objects such as tables can vary depending on the target database. Each database engine has specific implementations for backing up the data.

Method 1: Using the SQL SELECT INTO Clause

If you are not familiar with it, the SELECT INTO clause allows us to create a copy of an existing table into a new table under a different name but with similar schema and data.

This provides a near universe method of backing up a table in SQL without needing for custom SQL queries and complex recursive data copy.

To backup an existing table using the SELECT INTO clause, we can run the query as follows:

SELECT *

INTO backup_table

FROM original_table

WHERE condition;

We can specify the following:

1. The “backup_table” as the name for the table where we wish to backup the data.
2. The “original_table” specifies the name of the data that we wish to backup.
3. The “condition” is an optional condition that filters for a specific data that you wish to backup.

Let us say we want to film a table from the Sakila sample database. We can use the SELECT INTO clause as shown in the following query:

SELECT *

INTO film_backup

FROM sakila.film;

This should create a new table called “film_backup” with the same table structure and objects as the film table and includes the data as the source table.

In some cases, you may want to copy the data into a table that has a similar table structure. You can modify the previous clause into an “insert” statement as follows:

INSERT INTO backup_table

SELECT *

FROM original_table

WHERE condition;

This copies the data into the new table. It is good to ensure that the backup table already exists and includes a similar structure as the source table.

Method 2: SQL Server BACKUP DATABASE

When it comes to SQL Server, we have access to a built-in BACKUP DATABASE command that allows us to backup an entire database including the tables as shown in the following syntax:

BACKUP DATABASE database_name

TO disk = 'backup_path'

WITH INIT;

This should backup the specified database into the specified file. This method is a bit overkill when you need to backup a single table.

An example is as follows:

BACKUP DATABASE sakila

TO disk = 'C:\Backup\sakila.bak'

WITH INIT;

This should backup the Sakila database into the “sakila.bak” file.

Method 3: SQL Server Bulk Copy

A more granular and efficient method for backing up a single table in SQL Server is using the Bulk Copy. This is an incredibly powerful utility that allows us to copy the data between SQL Server tables and files.

We can use it to export a table as backup. The command syntax is as follows:

bcp database_name.schema_name.table_name out backup_file.txt -T -c

In the given syntax:

• The “database_name.schema_name.table_name” specifies the FQN (Fully Qualified Name) for the target table.
• The “backup_file.txt” specifies the destination file.
• The “-T” tells the SQL Server to use the Windows authentication. You can change to your desired authentication method.
• The “-c” specifies the character data type.

An example command is as follows:

bcp sakila.dbo.film out C:\Backup\film_backup.txt -T -c

This should export the data from the film table into the “film_backup.txt” file.

Conclusion

In this tutorial, we learned how to use the various SQL tools and queries to backup a table. It is good to keep that each database engine offers specific tools for backing up the data.

View the full article