Migrate SQL Server data from one database to another

Question

We have 2 servers with a database containing the same tables and structures but different data. One is the testing environment, the other one contains productive data.

We want to copy the data from the productive database to the testing database. What is the best approach to achieve this?

If I delete the data first; will I be able to insert the data? Or will the primary key's count from where they were? What about inserting the primary keys for tables that have autonumbering?

The easiest way to do this is to use a specialized tool for this, like Red Gate Software's SQL Data Compare which allows you to "diff" and "sync" two database tables in a visual manner — marc_s
– marc_s, Commented Dec 7, 2012 at 10:32

Kane · Accepted Answer · 2012-12-07 10:31:31Z

1

It will depend on your specific need and data structure but here are some options to think over (prioritised in terms of what I would recommend):-

A simple backup and restore will be the easiest and quickest solution;
Using a data scripting tool (like Red-Gate's Data Compare) could solve your needs;
A SSIS package could be developed to pump data back and forth between the two instances; or
Write your own script using the SET IDENTITY INSERT ON / OFF command for the identity seeded tables

answered Dec 7, 2012 at 10:31

Kane

16.8k11 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anonymoose Over a year ago

I will have a look at Red-Gate, Thank you

Collectives™ on Stack Overflow

Migrate SQL Server data from one database to another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related