Safely Perform MS SQL Server Single Table Backup [6 Ways]

MSSQL Table Backup vs. SQL Database Backup

When it comes to SQL Server backup, it often refers to backing up the entire database, which contains all the data, logs, etc. and can take up plenty disk space.

Thus, you may wonder, can you back up only a single table or two in a SQL database, especially when you only made minor changes to them? This is actually not a rare question. But just to let you know, MSSQL Server table backup with data cannot replace the regular SQL Server backup.

This is because a table may depend on other tables via foreign key relationships, this operation may compromise the table’s schema integrity, and even lead to data loss. You may consider MSSQL backup table only if you are aware of the riskiness and are in one of the following situations:

• The specific table is independent and complete.
• Except for the data and the schema, you don’t need to worry about anything else.
• Before performing data import/export, or migrating data in tables.
• Before performing a small number of add, delete, or modify operations on one single table.

How to Perform MSSQL Server Table Backup (Specific Table)

Currently there’s no directly way for MSSQL backup table only, but we can achieve similar results in other ways. For example, make a copy, or export the data in tables. Generally, you can achieve this either through SSMS or using Bulk Copy Program (BCP), and each with 3 specific approaches.

✦ MSSQL Table Backup via SQL Server Management Studio (SSMS):

Way 1. Use SELECT INTO Statement to Copy SQL Tables: The SELECT INTO statement is used to create a new table, and insert correspondent data from query into it. Please note that it won’t copy the indexes, keys, and constraints to the new table.

Way 2. Generate Scripts in SSMS to Backup Tables: With simple clicks SSMS can generate table copying scripts automatically. In this way you can also copy the table to another SQL Server.

Way 3. Use SQL Server Import and Export Wizard to Backup Tables: This is the simplest non-script way to export your table. You can export the table to another database or server.

✦ MS SQL Table Backup via Bulk Copy Program (BCP):

The following 3 ways are based on the same principle, but using different tools. You can choose one of them according to the tool you prefer. In these ways, you must have bulk import and export privileges.

Way 4. Run the BCP Commands on Command Prompt Window

Way 5. Run the BCP Commands on PowerShell

Way 6. Run the BCP Commands on SQL Server Data Tools (SSDT)

Way 1. Use SELECT INTO Statement to Copy SQL Tables

Launch SSMS and connect to your instance. Click New Query on the upper bar, input one of the following statements according to your needs. Click Execute to start copy.

♦ Create a new target table and insert the data of original table into it.

use DatabaseName
go
select * into TargetTable from OriginalTable
go

♦ Copy and insert the data in original table to an existing target table, and it won’t affect the existing data in 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

Note:
TargetName: You can specify the name of the new target table in query (not the same as an existing table).
Where 1=2: 1=2 is evaluated as FALSE for all rows in the original table, which means it will filter all the data out, and create a new table with only the same table structure.

Way 2. Generate Scripts in SSMS to Backup Tables

1. Launch SSMS and connect to your instance. Right-click the database (instead of the table) which you need to perform table backup, and select Tasks>Generate Scripts...

2. Click Next to go to Choose Objects page, select specific database objects, and then check the tables you need to backup.

Click Next.

3. In Set Scripting Options page, Click Advanced. It will pop up an Advanced Scripting Options window.

4. Find Types of data to script, and select Scheme and data next to it.

There are also Data only, and Schema only options for you to choose as need.

Click OK to finish the settings.

5. Back to Set Scripting Options page, select Open in new query window, and click Next to go to the Summary.

6. The Summary of all your settings is presented in the window. Now you can click Next to Save Scripts.

7. When it’s done, click Finish to close the window.

8. Back to the SSMS interface, you can see that the script has been generated automatically.

Change the database name in the front line, to copy the table to another database or server. Or you will come to “Database already exist” error.

Click Execute to run it.

Way 3. Use SQL Server Import and Export Wizard to Backup Tables

1. Launch SSMS and connect to your instance. Right-click the database (instead of table) that needs to perform table backup, and select Tasks>Export Data...

2. In Choose a Data Source page, select SQL Server Native Client 11.0 in the dropping menu of Data source.

3. Select your instance in Server name. Choose an Authentication way and input correspondent information.

Select the Database you need to perform table backup. And then you can click Next.

4. In Choose a Destination page, same as the previous step. Select your instance in Server name and input Authentication information.

Select a destination database where you want to copy the table to.

Click Next.

5. In Specify Table Copy or Query page, select Copy data from one or more tables or views, to only copy the data.

If you need you can also select Write a query to specify the data to transfer.

Then click Next.

6. In Select Source Tables and Views page, check the specific tables you want to export.

And click Next.

7. In Save and Run Package page, check Run immediately.

And you can also check Save SSIS Package to encrypt your important data. The SSIS package can also be used in other instances.

Then click Next to save the settings for the SSIS package.

8. Now you can verify the settings of what the wizard will do. Click Finish to execute the table backup.

9. The MS SQL table backup execution is successful.

Way 4. Run the BCP Commands via Command Prompt Window

Use Win+R windows shortcut to open the Run Command window, and then input cmd to open Command Prompt window. Input the following commands and press Enter to start.

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

Note:
1.In Filepath you need to write where you want to put the exported backup in.
2.The three parameters of the above command:
-c: Specifies that the char type is used as the storage type.
-T: Specifies that bcp uses a trusted connection to log in to the SQL Server database.
-S: Specifies the instance name to connect to.
3.If you want to know more about the parameters, you can input bcp in the Command Prompt window and press Enter to query all of them.

Here’s an example exports the table Date_max from the database DBname to a file named Date_max_001.bcp.

Way 5. Run the BCP Commands on Powershell to Export Table

Launch PowerShell ISE (you can find it by input PowerShell ISE in Run window) and run it as administrator. Input the following commands. Click Execute on the upper bar to run it.

$db = "DatabaseName"
$schema = "SchemaName"
$table = "TableName"
$path = "Filepath"
$psCommand = "bcp $($db). $($schema). $($table) out $path -T -c -S InstanceName"
Invoke-Expression $psCommand

Way 6. Run BCP Commands on SQL Server Data Tools (SSDT)

1. Launch SSDT and click New Project.

2. Select Business Intelligence > Integration Services > Integration Services Project. And fill in the Name, Location, and Solution name below, then click OK.

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

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

For example: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

Then input the following table-exporting bcp commands next to Arguments.

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

Then click OK to finish the settings.

5. Right-click this task and select Execute Task.

6. The export is accomplished.

Summary

In this article, I introduced 6 ways in total for SQL Server backup table.

However, since single MSSQL table backup can lead to data loss, I still suggest you to back up the entire database regularly. It’s easy to operate and ensures data integrity.

If you think full backups are space-consuming, you can also create differential backups, or compressed backups. And if you think the operations are too laborious, and don’t want to waste time on them. I suggest you use AOMEI Cyber Backup to created SQL Server database backups automatically. It provides flexible backup methods, capable of performing full / incremental / differential backups, which can save more space.

Besides, it allows you to control all SQL databases and virtual machines within LAN from a central console. In this way, you can backup SQL database to remote location. For enterprises, it can prompt efficiency and reduce costs.