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)?
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.
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.
@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.
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).
