It is time to overview table-level backup - one of the most rarely used data protection types. You may need to copy data into one single table in case of data export or migration, or as part of a disaster recovery plan.
In this article, you will find out about how to backup a table in SQL Server and what you need to remember.
Why Backup a Table in SQL Server
In most cases regular, database-level backups are more suitable and easier to use, even in cases where you may want to restore a single table. But there are a few scenarios when table backup is a reasonable approach:
- Frequent backup of a large important table(s). When dealing with terabyte-sized databases, it can be exceptionally difficult creating a full DB backup promptly. Therefore, you can create a separate table copy prior to 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 possibility for the worst cases of data corruption.
- Data import/export in cases of complex data migration, for example.
Table-level backup doesn’t care about cross-table dependencies. So a good idea here is to create a table-level backup type specifically catered to “independent” tables or choose regular DB-level backups. Otherwise, you will have the headache of having to recreate dependencies manually. Though, there are scripts allowing you to save the DB schema, thus minimizing your recovery time.
Now, let's outline one of the easiest ways to create table-level backup.
How to Backup a Table in SQL Server Using Bulk Copy Program
Bulk Copy Program (BCP) is a command-line tool for moving the data to or from SQL Server. If you have bulk import-export privileges - then you can use BCP for table backup. Below are the necessary configuration steps:
- First of all, you need to enable xp_cmdshell component, as it may be disabled by default. Use these commands consistently in SQL Query Analyzer:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO
- Now we can create a backup. Locate the desired database table and use 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, and 'C:\Backup\' in @file - with the target folder (the script will create new backup files with unique names in this folder on each backup run).
-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. Native format is intended for high-speed data transfer of data between SQL Server tables.
After the script has finished working, you will see something similar to the results window:
I have copied a small system table 'dbo.spt_monitor' in the master database, as an example. In a working environment with a larger database, you will find more info in the results window. The key point here being that you should not see any errors after the script run.
- 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 (highlighted) with your own details.
In most cases, you will definitely prefer regular DB-level backups, thanks to their simplicity and wide-spread support with any SQL backup tool. But in some scenarios, you may only need to recover the data from one particular table. You can prepare for such situations by creating a backup of a table in SQL Server using, for example, BCP tool.
But keep in mind that table-level data copy does not include dependencies with other tables, embedded SQL objects, schema info, etc. So you simply cannot use it as a replacement for a regular backup.