How to take a back up of single table and restore it back when I required.
I mean,I need back of single table with all the indexes,constraints and data associated with that table. because I want to modify some of the data of the base table,before doing any modifications to the base table, I want to take backup. when I use select * into table2 from table1 where 1=1 giving the table and its data, but the constraints and indexes associated with that table are not coming.
You'll either have to create a manual method for it (stored procedure or something) or purchase third party backup software to do object level backup. SQL Server doesn't support it natively. Quest's Litespeed (enterprise edition) has object level backup. Redgate's SQL Backup may as well but I'm not as familiar with it.
Note, that if you are ok with having a backup of your entire database (which will include your table in question and all indexes, triggers, keys, etc) then you can use SQL Server's native backup functionality. You will have to restore the entire database to get your single table back, but it'll be there in the state from when you backed it up.
To export:
bcp "[MyDatabase].dbo.Customer" out "Customer.bcp" -N -S ServerName -T -E -U"xxxxxx" -P"xxxxxxx"
To import:
bcp "[MyDatabase].dbo.Customer" in "Customer.bcp" -N -S ServerName -T -E -b 10000 -U"xxxxxx" -P"xxxxxxx"
You need to use the backup facility. Define a maintenance plan in SQL Server Management Studio, and configure it to only backup one table in that database. Then run the maintenance task manually.
Frankly I'd go with squillman's approach. You can restore the backup database to a new database and port information across, so this isn't too much of a limit. Without going to a lot of trouble or spending a lot of money this might well be as good as it gets.
Or for that matter, copy the table to a new DB and back that up.
