Backup a single table with its data from a database in sql server 2008

There are many ways you can take back of table.

  1. BCP (BULK COPY PROGRAM)
  2. Generate Table Script with data
  3. Make a copy of table using SELECT INTO, example here
  4. SAVE Table Data Directly in a Flat file
  5. Export Data using SSIS to any destination

You can use the "Generate script for database objects" feature on SSMS.

  1. Right click on the target database
  2. Select Tasks > Generate Scripts
  3. Choose desired table or specific object
  4. Hit the Advanced button
  5. Under General, choose value on the Types of data to script. You can select Data only, Schema only, and Schema and data. Schema and data includes both table creation and actual data on the generated script.
  6. Click Next until wizard is done

This one solved my challenge.
Hope this will help you as well.


select * into mytable_backup from mytable

Makes a copy of table mytable, and every row in it, called mytable_backup.