Sql-server – Microsoft SQL server backup : restoring data to one table

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data"

TIP: In the final step select "Script to a New Query Window", it'll work much faster that way.

From the command prompt:

bcp DBName.dbo.TableName OUT c:\TableName.bcp -N -T -Smydbserver\instance

Then, drop and re-create your tables (I don't think there's any simple way of doing this).

To get your data back,

bcp DBName.dbo.TableName IN c:\TableName.bcp -N -T -Smydbserver\instance

You will need to repeat this for each table. It will give you a binary copy of the data, which is then re-inserted. Just as long as each field is the same length as the old one, you shouldn't have any troubles re-inserting the data on a different schema.