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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Oct 10 '12 at 16:14

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

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 – NoNaMe Oct 11 '12 at 4:27
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

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

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

protected by Community Jul 23 '15 at 14:38

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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