Taking backup of data in a table.

Question

Hi All,

Is there some way to backup the data in a table in sql server by using sql scripts. Note: I dont want to take the backup of the whole database.

And later on restore the same data back in case of any failure. Please suggest some solution.

Answer 1

It depends on how you want to have the backup. You can create a new table as the result on a query, which looks like this: CREATE TABLE foobar AS SELECT bar, baz FROM foo;.

You can also look in the documentation of your DB if the bachup programms accept the dump of specified tables only. PostgreSQL can do this with the --table parameter, mysql seems to do this when the table names are listed after the database name.

Answer 2

For Microsoft SQL Server 2005/2008 SSMS Tools Pack has a "Generate Insert Statements" function that can script out all the data in a table.

Answer 3

1 Generate script of the table

2 Bcp out the data to text file