How to Easily Backup a Table in SQL Server in 3 Ways?

Data is the lifeblood of an organization, and ensuring its integrity and availability is critical. Having a reliable backup strategy in place is an important way to keep your data safe. For SQL Server databases, except for database backup, table backup is an important practice to protect critical data from accidental loss.

However, one may wonder why it is necessary to backup only specific tables and not the entire database. A table is similar to a spreadsheet for organizing and storing data while a database is similar to a folder and can contain multiple data tables.

The database provides the overall management of data and the ability to access it, while the table is where the actual data is stored, and through the operation of the table you can realize the addition, deletion, modification, and checking of the data and other operations.

Backing up SQL tables provides the flexibility to centralize backups of critical data, reducing storage requirements and simplifying the backup process.

Benefits of backing up SQL Server table

The benefits are profound, as it optimizes resource usage while effectively protecting critical information. Let's deep dive into some specific scenarios where backing up a table in SQL Server is essential:

Disaster Recovery Strategy: When implementing a disaster recovery strategy, downtime can be reduced, and critical data can be recovered faster with separate table backups.

Large Database Management：When dealing with a large database, backing up only the necessary tables to protect sensitive or specific data can save storage space and time.

Efficient Data Transfer: When you need to move or copy a specific table to a different server or location, backing up only that table can significantly improve the efficiency of data transfer.

Frequent Table Updates: When a table is frequently updated or changed, creating a separate backup ensures having the most up-to-date data without performing a full database backup.

Apply SQL Server scripts to backup a table

Scripts can be used to automate the backup process, thereby increasing efficiency and ensure consistency in the backup process, avoiding the risk of making errors that can easily be made during manual operations.

To take backup of a table in SQL Server, following stepts below:

1. In SSMS, connect to an instance> right-click the database> select Tasks> choose Generate Scripts

2. Click Next> select specific database objects> check the tables you need to backup> click Next

3. Click Advanced> Find Types of data to script> select Scheme and data next to it> click OK

4. Hit Open in new query window> click Next> click Next> click Finish

5. Return to SSMS interface> change the database name in a script> click Execute

Please be careful to distinguish between .sql file and .bak file. When you backup tables using SQL Server scripts, a .sql file is generated. It is used to backup tables and their data. However, a full database backup created using SQL Server's backup and recovery features generates a .bak file. It is used to backup the entire database.

Use SELECT INTO statement to backup SQL Server table

The SELECT INTO Statement is a SQL statement used to select data from one table and insert it into a new table.

Table backup using SELECT INTO statement is very simple, you only need to specify the name of the backup table, the name of the original table. And there is no need to manually create the structure of the backup table. The backup takes very little time.

To back up a table in SQL Server, following stepts below:

1. In SSMS, connect to an instance

2. Click New Query and enter the following statements as required:

Make a new target table and insert the previous table's data into it:

     use DatabaseName

     go

     select * into TargetTable from OriginalTable

     go

Copy and paste the data from the original table into an existing target table:

use DatabaseName

go

insert into TargetTable select * from OriginalTable

go

Copy only the structure of the original table into the target table:

use DatabaseName

go

select * into TargetTable from OriginalTable Where 1=2

go

3. Click Execute

Backing up a table using the SELECT INTO statement generates a backup file that you can save for future restores.

Use Bulk Copy Program (BCP) for SQL Server table backup

Bulk Copy Program (BCP) is a command line utility that can be used to copy data from a SQL Server database to a file or from a file to a SQL Server database. BCP is commonly used to import and export data or backup and restore data.

By using BCP to backup SQL tables, you can significantly improve the backup efficiency. Here are 3 ways to use BCP, you can choose according to your needs.

This will allow you to specify the tables to be backed up, the path and name of the backup file, and so on, as needed. By writing scripts, you can automate the backup process, thus increasing efficiency and reducing errors.

You can start Windows Command Prompt to use this program.

To create a backup of a table in SQL Server, following stepts below:

In Windows, press Win + R> type cmd> enter the following commands> press Enter

bcp DatabaseName.SchemaName.TableName out Filepath -c -T -S InstanceName

Note:

1.In Filepath, write the Location for exporting backup

2.-c: The char type is specified as the storage type

-T: Specifies that bcp connects to the SQL Server database via a trustworthy connection

-S: Specifies the name of the instance to connect to

If you prefer PowerShell command, just these steps below:

In the windows search window type PowerShell ISE> right-click it> choose run as administrator>

input the following commands> click Execute

$db = "DatabaseName"

$schema = "SchemaName"

$table = "TableName"

$path = "Filepath"

$psCommand = "bcp $($db). $($schema). $($table) out $path -T -c -S InstanceName"

Invoke-Expression $psCommand

This program can also work with SSDT (SQL Server Data Tools), which is an integrated development environment (IDE) that can be used for SQL Server database development and can be integrated with Visual Studio.

To backup a table in SQL Server, follow the steps below:

1. Open SSDT> click New Project

2. Select Business Intelligence> choose Integration Services > click Integration Services Project> fill Name, Location, and Solution name below> click OK

3. Select and drag Execute Process Task into the right blank> Double-click it to open the Execute Process Task Editor windqow

4. Input the file path of bcp.exe next to Executable

5. Input “DatabaseName.SchemaName.TableName out Filepath -c -T -S InstanceName” next to Arguments> click OK

6. Right-click the task> select Execute Task

Try the integrated SQL Server backup solution Vinchin Backup & Recovery

Although backing up a table in SQL Server is one way of backing up a database, backing up the entire database on a regular basis ensures overall data security.

Vinchin Backup & Recovery is a professional enterprise data backup and recovery software that supports backup and recovery of SQL Server 2008 2012 2014 2016 2017 2019 and Oracle, MySQL and etc.

Besides, Vinchin provides an automated backup function that can perform backup tasks automatically. Of course, Vinchin can also create differential backups or compressed backups if you think a full backup will take up space. Regarding security, Vinchin supports encrypted backup, which can guarantee the security of backup data.

1. Choose Physical Backup> click Database Backup> select Backup> select a SQL Server> click Next

2. Specify the Destination> hit Next

3. Define Backup strategies> hit Next

4. Hit Submit

Vinchin provides a 60-day free trial. You can contact us or a local partner directly for a customized solution! If you have any troubles while operating, we will provide online help.

Conclusion

In summary, backing up specific SQL Server tables is crucial for protecting critical data and three methods for backing up single table including using SQL Server scripts, SELECT INTO statement, and Bulk Copy Program (BCP).

Vinchin Backup & Recovery is a professional enterprise data backup software that automates backup tasks and supports encrypted backup. Just try it for a customized backup solution and peace of mind knowing your data is safe.