THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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: http://msdn.microsoft.com/en-us/library/ms191516.aspx

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 03, 2010 6:51 AM by BuckWoody
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement