Table-level backup is one of the most rarely used data protection types in Microsoft SQL Server. However, SQL Server table backup is important in certain scenarios; for example, you may need to copy data into one single table in order to perform a data export or migration, or as part of a disaster recovery plan.
Table of Contents
When Do You Need SQL Server Table Backup?
Generally speaking, backing up a single table instead of your entire database, is not desirable. However, there are a few scenarios when this approach is reasonable:
- Performing frequent backups of large table(s). When you are dealing with terabyte-sized databases, it can be exceptionally difficult to create a full database backup quickly. In this case, you can create a separate copy of the specific table in order to back it up prior to performing any server maintenance, massive data changes or version upgrades.
- As a part of a complex disaster recovery plan. Despite having a distributed data cluster and offsite backup, you may want to have a “last resort” recovery resource in the form of a specific table backup.
- Performing data import or export. In certain cases that require complex data migration, it may be useful to be able to import or export a specific table instead of an entire database.
If you back up a single SQL Server table that is dependent on other tables, its schema integrity will be compromised. Therefore, it makes sense to back up a single table if it is a standalone table or if you don’t care about anything other than the data and the table structure. Otherwise, you will have to recreate dependencies manually or use scripts to save the database schema.
How to Perform an SQL Server Table Backup Using the Bulk Copy Program (BCP)
Bulk Copy Program (BCP) is a command-line tool for moving data to or from SQL Server. If you have bulk import-export privileges, you can use BCP.
Following are the necessary BCP configuration steps:
1. Enable xp_cmdshell component, as it may be disabled by default. Do so by running these commands in SQL Query Analyzer:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO
2. You can now create a backup by locating the desired database table and using its name in the script below:
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
In this example you need to change the quoted @table parameter to your table name. You also need to change 'C:\Backup\' in @file to the target folder (the script will create new backup files with unique names in this folder on each backup run).
The -n option tells SQL to use native data types, and -T allows use of Windows Authentication. If you prefer to use SQL authentication, then use -U -P with your username and password instead. The native format is intended for high-speed transfer of data between SQL Server tables.
3. After the script has finished working, you will see something similar to the results window below:
Above, we've copied a small system table named 'dbo.spt_monitor' in the master database, as an example. In a working environment with a larger database, you will find more information in the results window. But if all goes as expected, you should not see any errors after the script run.
4. You can recover the previously backed up table using the command below:
bcp dbo.spt_monitor IN "C:\Backup\dbo.spt_monitor_20180801.bak" -T -n
-n denotes use of native SQL format, and -T instructs the use of a trusted connection. Of course, don’t forget to replace the table name and backup path with your own configuration details.
Summary
In most cases, you will want to rely on regular database backup for your data backup and recovery needs. It is a simpler approach that is supported by any standard backup tool.
In some scenarios, however, you may only need to recover the data from one particular table. You can prepare for such situations by creating a SQL Server table backup using, for example, the BCP tool.
That said, keep in mind that table-level data copy does not include dependencies with other tables, embedded SQL objects, schema info, and other important components. For that reason, you cannot use table-level backup as a replacement for a regular backup.
Simple. Reliable.
