THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

Back up a single table in SQL Server

SQL Server doesn’t have an easy way to take a table backup, so I often use the bcp (Bulk Copy Program) to accomplish the same goal. I’ve mentioned this before, and someone told me when they tried it they couldn’t restore the table – ah the dangers of telling people half the information! I should have mentioned that you need to have a “format file” ready if the table does not exist at the destination. In my case I already had the table, in this person’s case they did not. The format file can be used to rebuild that table structure before the data is bcp’d in, and you can read more about it here:

There’s another way to back up a table, and that’s to create a Filegroup and place the table there. Then you can take a Filegroup backup to back up a single table.

Of course, there are other methods of moving a single table’s data in an out, including SQL Server Integration Services and even the older Data Transformation Services, or simply by using hte SQLCMD or PowerShell utilities to run a query and just save the output to a file. In fact, these days I’m using a PowerShell script to build INSERT statements from that query. That could also easily be modified to create the table structure (or modify one if needed) quite easily.

Published Thursday, June 3, 2010 6:51 AM by BuckWoody
Filed under: , ,



Michael K. Campbell said:

With SQL Server 2008 you can also just script the table and specify the option to script the data as well.

Obviously, that will turn into one ugly script/file if the table is huge... but it's nice to finally have something akin to the 'DUMP' command from other DBMSes.

June 3, 2010 1:14 PM

Amit Pandey said:

I feel that

select * into new_table

is the easiest way when we only have to transfer the data.

June 3, 2010 10:48 PM

mike lester said:

actually;  the simple way ( works for 2005, 2008 etc )  

if your talking about making a quick table copy.

(ignore all the ubiquitous crap about 'not recommended..'  ninney's)

just take a note of any additional column based objects, constraints etc; and you'll be fine.

select top 0 * into MyTableCopy from MyOriginalTable

set identity_insert 'MyTableCopy' on

insert into MyTableCopy (col1,col2,col3...)

select * from MyOriginalTable

set identity_insert 'MyTableCopy' off

BANG!!  All done with one simple script.  Simple duplicate table.

August 18, 2010 12:35 PM

BuckWoody said:

Mike - We're talking more here about backing up a table - not copying one. But thanks!

September 13, 2010 5:17 PM

Mike... THANKS! said:

Simple and quick.  That is exactly what I was looking for.

Much appreciated.

October 15, 2013 9:12 AM

Why not? said:

Table copies work perfectly as a table backup.

Mike has a point, and this methodology is used quite frequently.

Associated objects can be created again if needed, but the data is whats important.  


Simple. Efficient.

June 20, 2014 2:02 PM

SELECT INTO is the most practical. said:

This is an old post, but a common question.  I see it's been answered already by Mike.  Even in a post 2014 world the SELECT INTO process is the most practical.

You can port it into another MSSQL Server with a single script (FQDN) database, and maintain via backups which are most likely scheduled already.  Port it back with single script to repopulate new object, or simply build another.

April 4, 2017 6:03 PM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement