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:
INTO backup_table
FROM original_table
WHERE condition;
We can specify the following:
- The “backup_table” as the name for the table where we wish to backup the data.
- The “original_table” specifies the name of the data that we wish to backup.
- 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:
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:
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:
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:
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:
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:
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.
