Taking a backup of a single table in the SQL Server

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.