question

Mark avatar image
Mark asked

Backup Individual Tables rather than the whole database?

I want to backup a handful of tables onto a USB drive. Most of the tables are inconsequential, only a small subset of the tables are needed, and the whole database is too big to back up this way. What's the best way to do this (for either SQL-2005 or SQL-2008)?
sql-server-2008sql-server-2005backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
TimothyAWiseman avatar image
TimothyAWiseman answered
The most direct and likely easiest way is to export them into a different data format. Of course there are a number of ways of doing this, but perhaps the easiest is using BCP into CSV files. If you want to maintain more of the structure such as data types, you can use SSIS or something similar to export to an Access or Excel file. This method will lose some of the schema and meta data associated with it, but if you are focused on only a few tables you are probably most (or even solely) concerned with the data. Another alternative if you want to perfectly maintain the schema, indexes, and other metadata is to create another database as an intermediate step, copy the tables there, and back that up.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SSIS to export data out is a very good option. It can be combined with **All Tasks** -> **Generate Scripts** feature to preserve both data and separately full definition of the tables including indexes etc. At new destination, you can first run the scripts to create all tables and their respective constraints and indexes and then SSIS to import data from the files with originally exported data.
3 Likes 3 ·
@Mark, it depends on your use case. Normally when I want a backup, I want the whole database. If I want to move or send just a subset of the data, then I generally don't want to do that via backup channels. If it is within one network, you may want to look at Red Gates SQL Compare and SQL Data Compare. If it is across networks, then transmitting Excel files has generally worked well for me.
3 Likes 3 ·
Thank you Timothy. I like your last suggestion best. But it surprises me that there isn't a better way to do this. It's like ON or OFF is good enough - as if MS is saying: "We command you to copy the entire database! No exceptions apply; we rule. End of story."
0 Likes 0 ·
I can try to pimp the url to the article about **generating insert statements**: http://www.sqlservercentral.com/articles/T-SQL/66987/ This can work in some cases when exporting records to Excel or other flat file is not trivial (such as varbinary(max) or image columns).
0 Likes 0 ·
@Oleg and @Timothy - Sorry for the delayed response and thank you both for your insights.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.