Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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.

share|improve this answer

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"
share|improve this answer

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.

share|improve this answer
    
Hi Wolfgang, Thks for your rply, but does this maintance plan backup tables index,constraint also ? –  Dhiva Aug 29 '10 at 13:18
    
I thought it did. Looking at sqillman's answer, I think I am wrong. You could definitelydo it with MySQL, but that's not your type of server. –  wolfgangsz Aug 30 '10 at 11:55

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.