דילוג לתוכן
Articles and Knowledge Centre / Management and Operations

Exporting a table in SQL Server: why it is not a backup of the database?

Need to extract a single table from SQL Server, not the entire database? There are situations where this is useful: data export, system migration, or an additional copy before a change. But here it’s important to pause for a moment: a file with table data is not a complete backup of the database. Let’s understand when such a copy…

12.10.2026 · 6-minute read
Illustration on exporting a table in SQL Server: Why it is not a backup for the database?

Do you need to export a single table from SQL Server, and not the entire database? There are situations where this is useful: data export, system migration, or an extra copy before making changes. But here it is important to pause for a moment: a file with table data is not a full database backup. Let's understand when such a copy is appropriate, how to use BCP, and what it leaves out.

A table is part of the database

  • Export preserves data
  • Structure and components are saved separately
  • Database backup is verified during restore
Key principles at a glance

When do you really need only one table?

In most backup and restore scenarios, a backup of the entire database is needed. A single table does not replace it. However, in the following cases, a copy of a table can be a useful tool:

  • Frequent copies of large tables: In terabyte-sized databases, creating a full backup quickly can be challenging. You can create a separate copy of a specific table before server maintenance, extensive data changes, or version upgrades.
  • An additional component in a complex recovery plan: Even when there is a distributed data cluster and off-site backup, you might want to keep a copy of a particular table as an additional last-resort option.
  • Importing or exporting information: In complex migration processes, it may be preferable to import or export a specific table rather than migrate the entire database.

A table may appear independent until it is discovered that it depends on three other tables. A copy of it alone does not preserve all relationships and database integrity. It is more suitable for an independent table or a defined need in data and structure; otherwise, dependencies must also be preserved and restored, for example using schema scripts.

Export with BCP — and check what has been created

BCP, short for Bulk Copy Program, is a command-line tool for transferring data to and from SQL Server. To export or import large amounts of data, appropriate permissions are also required — not just the correct command.

We will present the source process, with marking of the required corrections. The commands here explain the example; they are not instructions to change a production server. In particular, there is no need to run xp_cmdshell to run bcp outside the SQL engine.

The first step in the source: Running xp_cmdshell

The source suggests enabling the xp_cmdshell component, which may be disabled by default, using the following commands in the SQL query editor:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

The second step in the source: Building the export command

After selecting the table, the source builds the export file name and runs the command from SQL Server:

DECLARE @table VARCHAR(128), @file VARCHAR(255), @cmd VARCHAR(512);
SET @table = 'dbo.spt_monitor';
SET @file = 'C:\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.dat';
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T';
EXEC master..xp_cmdshell @cmd;

Replace the table name in the @table variable with the desired table name, and replace the C:Backup folder with the target folder. In the example, a date in the year, month, and day format is added to the file name.

Corrected export example from the command line, without running xp_cmdshell:

bcp ExampleDB.dbo.ExampleTable out "C:/Backup/example-table.dat" -S SERVERNAME -T -n

Replace ExampleDB, the table name, SERVERNAME, and the path with the appropriate values. -T uses Windows authentication; -n selects the native data format. The execution account must have appropriate permissions, and the file path refers to the computer where bcp is run.

Step three: Check the execution results

In the example, the small table dbo.spt_monitor was copied from the master database. In a larger table, more data will appear in the results. Check that there are no errors and that the number of rows matches expectations. The fact that a file was created in the folder does not mean that everything needed is already in it.

The fourth step in the source: importing the data back

The source presents the following command for importing the copy:

bcp dbo.spt_monitor IN "C:\Backup\dbo.spt_monitor_20180801.bak" -T -n

Useful copy, with clear boundaries

For regular backup and restore purposes, backing up the database is generally the more suitable and simpler method, with support from the appropriate backup tools.

When only data from a specific table is needed, you can create a copy using BCP or another suitable tool. Remember what doesn’t come with it: dependencies on other tables, additional SQL objects, schema, and important components. It’s a useful tool in the toolbox — but not the entire backup kit.

Source for correction notes: Microsoft documentation for the BCP tool andConfiguring xp_cmdshell.

Continue reading