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.
Best Answer
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.