Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know how to take a backup of a database in SQL Server and restore it. But how can I take a backup of a single table, and then restore it? Is it possible?

I have a database with two different names (some different data) and I have to restore one table by taking it from one database to the other. How can I take a single table backup from one database and then restore it to the other one?

share|improve this question

migrated from stackoverflow.com Oct 10 '12 at 16:14

This question came from our site for professional and enthusiast programmers.

5  
You can't - backup is all or nothing. You can export the table structure using the Script database object functionality in SQL Server Management Studio, and you can export the data from a table using e.g. the bcp command line utility - but there's no "single-table" backup. –  marc_s Oct 10 '12 at 12:48
    
Are there any foreign keys on the table? That complicates the restore process if there is. –  cfradenburg Oct 10 '12 at 19:19
    
@cfradenburg No there is not any –  zzzz Oct 11 '12 at 4:27
add comment

3 Answers

up vote 2 down vote accepted

You Can Use Export/Import Functionality in Available in Sql Server. remember to check drop target table or somethng like that, checkbox

SQL Server Import and Export

OR Create Insert script on Source data and then RUN it on the Target database, after deleting the existing records from the source table as

SELECT 'insert into table1 (fielda,fieldb,fieldc) values (''' + fielda + ''',''' + fieldb + ''',''' + fieldc + ''')' AS InsertCmd 
FROM table1
share|improve this answer
add comment

Right click the database; Tasks, Generate Scripts & enable Script Data from the options. Select the table and Finish.

This will generate a CREATE TABLE followed by INSERTs for the data.

share|improve this answer
add comment

You could use a third-party product such as Red-Gate's SQL Object Level Recovery, that'll allow for you to recover a single object from a native SQL Server backup.

If it's a one-off, you can get a 14 day free trial.

share|improve this answer
add comment

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.