Migrate SQL Server data from one database to another

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?

Solution

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